Respostas (7)
Descobri que quando trabalhava com mesas, isto não funcionava. O filtro não estava na folha mas na mesa. este código fez o truque
Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1")
.AutoFilter.ApplyFilter
End With
End Sub
encontrei a informação aqui: http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp
Solução encontrada!
Clique com o botão direito do rato no nome da folha, escolha “Ver Código” e cole o código abaixo. Depois de colar, clique no ícone Excel abaixo de “Ficheiro” no canto superior esquerdo, ou digite Alt-F11, para voltar à vista da folha de cálculo.
Isto irá permitir a actualização automática. Não se esqueça de guardar o ficheiro num formato com suporte de macro mentira .xlsm.
Private Sub Worksheet_Change(ByVal Target As Range)
If Me.FilterMode = True Then
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With ActiveWorkbook
.CustomViews.Add ViewName:="Mine", RowColSettings:=True
Me.AutoFilterMode = False
.CustomViews("Mine").Show
.CustomViews("Mine").Delete
End With
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If
End Sub
Também uso uma VBA/Macro baseada em Worksheet_Change evento, mas a minha abordagem é ligeiramente diferente… Ok, primeiro o código e depois as explicações:
Private Sub Worksheet_Change(ByVal Target As Range)
' first remove filter
ActiveSheet.Range("$L$1:$L$126").AutoFilter Field:=1
' then apply it again
ActiveSheet.Range("$L$1:$L$126").AutoFilter Field:=1, Criteria1:="<>0"
End Sub
(Utilize a combinação de teclas Alt+F11 para fazer aparecer o painel de desenvolvimento e cole o código na folha de trabalho que contém o filtro que pretende obter auto-refresco).
No meu exemplo, presumo ter um filtro simples numa única coluna (L no meu caso) e que o meu intervalo de dados está nas linhas de 1 (mesmo que possa conter cabeçalho) a 126 (escolha um número suficientemente grande para ter a certeza). A operação é simples: à medida que algo é mudado na minha folha, o filtro na gama especificada é removido/reaplicado de novo para que seja renovado. O que precisa de um pouco de explicação aqui são Campo e Critérios.
O Campo é um offset inteiro do intervalo. No meu caso, só tenho um filtro de coluna única e o intervalo é feito por uma única coluna (L) que é a primeira no intervalo (por isso uso 1 como valor).
O Critério* é uma string que descreve o filtro a aplicar ao intervalo de dados. No meu exemplo, quero mostrar apenas linhas em que a coluna L difere de 0 (por isso, usei “<>0”).
É tudo. Para mais referências sobre o método Range.AutoFilter, ver: https://msdn.microsoft.com/en-us/library/office/ff193884.aspx
Só para consolidar a(s) resposta(s):
Sorin diz:
Clique direito no nome da sua folha, escolha “Ver Código” e cole o código abaixo. Depois de colar, clique no ícone Excel abaixo de “Ficheiro” no canto superior esquerdo, ou digite Alt-F11, para voltar à vista da folha de cálculo.
Isto irá permitir a actualização automática. Não se esqueça de guardar o ficheiro num formato com suporte macro .xlsm.
E Chris usou este código (o que acabei de fazer em 2010):
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.AutoFilter.ApplyFilter
End Sub
Se não expandir o post, só verá a resposta longa! ;)
Desculpe, representante insuficiente para comentar. (Admins, sinta-se à vontade para cortar isto num comentário acima.) Resposta do utilizador “danicotra” começando com “Eu uso um VBA/Macro baseado na folha de trabalho_Alterar evento também, mas a minha abordagem…” com ‘ primeiro remover filtro ’ depois aplicá-lo novamente é a solução correcta quando se usa Excel 2007+. Contudo .AutoFilter.ApplyFilter é inválido em XL03 e antes, por isso mostro o caminho abaixo.
& imploro que os verdadeiros peritos e gurus leiam o código porque estou bastante confiante de que é material de prateleira superior. Talvez a inexplicável contagem decrescente desta resposta possa ser invertida quando as pessoas virem o que de bom é feito abaixo.
danicotra utilizou um exemplo simplificado. Na verdade, pode fazer isto de forma mais geral. Assumir com ActiveSheet para o seguinte (ou algum outro objecto de folha):
Guardar o intervalo do autofiltro. Tem colunas .AutoFilter.Filters.Count, e linhas (.AutoFilter.Range.Count/.AutoFilter.Filters.Count), guardadas em rngAutofilter
Recolher numa matriz myAutofilters cada uma das 4 propriedades de cada um dos .AutoFilter.Filters.Count autofilter Items, tendo o cuidado de evitar “erros definidos pela aplicação” quando .On ou .Operator é falso. (myAutofilters seria reDim’d para o número de linhas e colunas no passo 1)
Desligar o filtro mas preservar os dropdowns com .ShowAllData
Para cada item de filtro que estava .On de acordo com a sua matriz guardada, reinicie 3 das 4 propriedades de cada um dos .AutoFilter.Filters.Count autofilter Items. Mais uma vez tenha cuidado para evitar “Erros definidos pela aplicação” quando o .Operator for falso, por isso para cada item “i”, rngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i,2) ou rngAutofilter. Campo AutoFilter:=i, Critério1:=myAutofilters(i,2), Operador:=myAutofilters(i,3), Critério2:=myAutofilters(i,4)
Agora o autofiltro será reinstituído, no mesmo intervalo que era antes do seu código começar, mas com o autofiltro actualizado para alterações nos dados.
Public myAutofilters As Variant, rngAutofilter As Range 'Public
Sub SaveAndRestoreAutofilters()
'This will update the autofilter display to recognize data changes by turning autofilter off and then on, preserving all characteristics
'Note, XL2007 and later have .autofilter.applyfilter, but not the invaluable XL03 and earlier
Dim i As Long, iNumAutofilters As Long, iNumActiveAutofilters As Long
iNumActiveAutofilters = SaveAutoFilterInfo(iNumAutofilters) 'NOTE! Use CALL or assignment to prevent parentheses from forcing ByVal !
If iNumActiveAutofilters < 1 Then
Application.StatusBar = "0 ACTIVE filters;" & iNumAutofilters & " autofilters"
Exit Sub
End If
ActiveSheet.ShowAllData
Rem Here optionally do stuff which can include changing data or toggling autofilter columns
For i = 1 To iNumAutofilters
If myAutofilters(i, 1) Then
If myAutofilters(i, 3) <> 0 Then 'then .Operator is something, so set it and Criteria2, else just Criteria1
rngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i, 2), Operator:=myAutofilters(i, 3), Criteria2:=myAutofilters(i, 4) ', On:=true by rule
Else
rngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i, 2) ', On:=true by rule (it's R/O anyway)
End If
Rem Selection.AutoFilter Field:=i 'How you'd "turn off" only a single column's autofiltering. FYI .On is R/O!
End If
'activesheet.autofiltermode=false 'just FYI, how you comprehensively turn off filtering on a sheet (erasing the dropdowns and criteria and filter range!)
Next i
End Sub
Function SaveAutoFilterInfo(iNumAutofilters As Long) As Long
Dim i As Long, iRowsAutofiltered As Long
SaveAutoFilterInfo = 0 'counts the number that are .On, and returns the total
iNumAutofilters = ActiveSheet.AutoFilter.Range.Columns.Count
If ActiveSheet.AutoFilter.Filters.Count <> iNumAutofilters Then MsgBox "I can't explain this. All bets are off. Aborting.": Exit function
ReDim myAutofilters(1 To iNumAutofilters, 4)
For i = 1 To iNumAutofilters
myAutofilters(i, 1) = ActiveSheet.AutoFilter.Filters(i).On
If myAutofilters(i, 1) Then
SaveAutoFilterInfo = SaveAutoFilterInfo + 1
myAutofilters(i, 2) = ActiveSheet.AutoFilter.Filters(i).Criteria1
myAutofilters(i, 3) = ActiveSheet.AutoFilter.Filters(i).Operator
If myAutofilters(i, 3) <> 0 Then 'then is either xlAnd, xlOr, etc., and there's a second criteria
myAutofilters(i, 4) = ActiveSheet.AutoFilter.Filters(i).Criteria2
End If
End If
Next i
iRowsAutofiltered = ActiveSheet.AutoFilter.Range.Count / ActiveSheet.AutoFilter.Range.Columns.Count
Set rngAutofilter = Cells(ActiveSheet.AutoFilter.Range.Row, ActiveSheet.AutoFilter.Range.Column).Resize(iRowsAutofiltered, iNumAutofilters)
End Function
using "data, from table"/power query in excel, which gives us option to refresh data when opening file. (also auto sort, and index column (number filtered rows automatically )) This will create result in another sheet. -select data required using mouse (rows and columns) -click on data tab, from table -in the last column, exclude blanks (optional, if you want to to display only filled cells) -add column, index column (optional, if you want to add row number to filtered results) -close and load to to edit again, click on query tab, and then on edit click on design tab in excel, on the arrow below refresh, connection properties, refresh data when opening file. adapted from: https://www.excelcampus.com/tips/sort-drop-down-lists-automatically/ part: 3. Sorting Drop Down Lists Using Power Query you can also copy data from sheet1 if not empty, for example field a1. copy this to a1 field in sheet2: =IF(Sheet1!A1"";Sheet1!A1;"")