Como atualizar automaticamente um filtro automático do Excel quando os dados são alterados?

Question

Como posso atualizar automaticamente um filtro automático do Excel quando os dados são alterados?

Caso de uso: altero o valor de uma célula para um valor que foi filtrado. Eu quero ver a linha atual desaparecendo sem ter que fazer mais nada.

    

Answer 1

Clique com o botão direito no nome da planilha, escolha "Ver código" e cole o código abaixo. Depois de colar, clique no ícone do Excel abaixo de "Arquivo" no canto superior esquerdo ou digite Alt-F11 para retornar à visualização da planilha.

Isso ativará a atualização automática. Não se esqueça de salvar o arquivo em um formato com o suporte de macro lie .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

    

Answer 2

Trocar o código com isso também parece funcionar (pelo menos no Excel 2010):

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

    

Answer 3

Descobri que, quando trabalhei com tabelas, isso não funcionou. O filtro não estava na folha, mas na mesa. esse 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 as informações aqui: link

    

Answer 4

Eu também uso um VBA / Macro baseado no evento Worksheet_Change , mas minha abordagem é um pouco 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

(Use a combinação de teclas Alt + F11 para que o painel de desenvolvimento apareça e cole o código na planilha que contém o filtro que você deseja atualizar automaticamente.)

No meu exemplo, suponho que tenha um filtro simples em uma única coluna (L no meu caso) e que meu intervalo de dados esteja em linhas de 1 (mesmo que possa conter cabeçalho) para 126 (escolha um número ótimo o suficiente para ter certeza). A operação é simples: quando algo é alterado na minha planilha, o filtro no intervalo especificado é removido / reaplicado novamente para que seja atualizado. O que precisa de um pouco de explicação aqui são Field e Critérios .

O Campo é um deslocamento inteiro do intervalo. No meu caso, eu só tenho um único filtro de coluna e o intervalo é feito por uma única coluna (L) que é a primeira do intervalo (portanto eu uso 1 como valor).

O Critérios é uma string que descreve o filtro a ser aplicado ao intervalo de dados. No meu exemplo, quero mostrar apenas linhas em que a coluna L difere de 0 (por isso usei "< > 0").

Isso é tudo. Para obter mais referências sobre o método Range.AutoFilter, consulte: link

    

Answer 5

Apenas para consolidar a (s) resposta (s):

Sorin diz:

Clique com o botão direito no nome da planilha, escolha "Ver código" e cole o código abaixo. Depois de colar, clique no ícone do Excel abaixo de "Arquivo" no canto superior esquerdo ou digite Alt-F11, para retornar à visualização da planilha.

Isso ativará a atualização automática. Não se esqueça de salvar o arquivo em um formato com suporte a macro .xlsm.

E Chris usou esse código (o que acabei de fazer em 2010):

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

Se você não expandir a postagem, verá apenas a resposta longa. ;)

    

Answer 6

Desculpe, representante insuficiente para comentar. (Admins, sinta-se livre para cortar isso em um comentário acima.) User "danicotra" resposta começando com "Eu uso um VBA / Macro com base no evento Worksheet_Change também, mas minha abordagem ..." com
    'primeiro remover filtro
    'em seguida, aplique-o novamente
é a solução correta ao usar o Excel 2007+. No entanto, .AutoFilter.ApplyFilter é inválido no XL03 e anterior, então mostro o caminho abaixo.

Eu imploro que verdadeiros especialistas e gurus leiam o código, porque estou bastante confiante de que ele é o material mais vendido. Talvez o inexplicável número negativo conte com essa resposta pode ser revertido quando as pessoas verem o que é bom feito abaixo.

danicotra usou um exemplo simplificado. Na verdade, você pode fazer isso de maneira mais geral. Suponha que com o ActiveSheet para o seguinte (ou algum outro objeto de pasta):

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

2. Colete em uma matriz myAutofilters de cada uma das 4 propriedades de cada um dos itens do autofilter .AutoFilter.Filters.Count, tomando cuidado para evitar "Erros definidos pelo aplicativo" quando .On ou .Operator for falso. (myAutofilters seria reDim'd para o número de linhas e colunas no passo 1)

3. Desative o filtro, mas preserve os menus suspensos com .ShowAllData

4. Para cada item de filtro que foi .Em acordo com sua matriz salva, redefina 3 das 4 propriedades de cada um dos itens do filtro automático .AutoFilter.Filters.Count. Novamente, tome cuidado para evitar "Erros definidos pelo aplicativo" quando .O operador é falso, portanto, para cada item "i",
   Campo rngAutofilter.AutoFilter: = i, Criteria1: = myAutofilters (i, 2)
   ou
   rngAutofilter.AutoFilter Field: = i, Criteria1: = myAutofilters (i, 2), Operador: = myAutofilters (i, 3), Criteria2: = myAutofilters (i, 4)

Agora, o filtro automático será reinstituído, na mesma faixa em que estava antes de seu código ser iniciado, mas com o filtro automático atualizado 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