Como fazer uma actualização automática de um auto-filtro Excel quando os dados são alterados?

Trocar o código com isto parece fazer o truque também (pelo menos no Excel 2010):

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

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

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

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

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! ;)

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;"")

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):

1. Guardar o intervalo do autofiltro. Tem colunas .AutoFilter.Filters.Count, e linhas (.AutoFilter.Range.Count/.AutoFilter.Filters.Count), guardadas em rngAutofilter

2. 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)

3. Desligar o filtro mas preservar os dropdowns com .ShowAllData

4. 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