Come aggiornare automaticamente un filtro automatico di Excel quando i dati vengono cambiati?

Scambiare il codice con questo sembra fare lo stesso (almeno in Excel 2010):

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

Ho scoperto che quando lavoravo con le tabelle, questo non funzionava. Il filtro non era sul foglio ma sulla tabella. questo codice ha fatto il trucco

Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1")
         .AutoFilter.ApplyFilter
    End With
End Sub

Ho trovato le informazioni qui: http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp

Anche io uso un VBA/Macro basato sull'evento Worksheet_Change, ma il mio approccio è leggermente diverso… Ok, prima il codice e poi le spiegazioni:

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

(Usa la combinazione di tasti Alt+F11 per far apparire il pannello di sviluppo e incolla il codice nel foglio di lavoro che contiene il filtro che vuoi far aggiornare automaticamente).

Nel mio esempio sto supponendo di avere un semplice filtro su una singola colonna (L nel mio caso) e che il mio intervallo di dati sia su righe da 1 (anche se potrebbe contenere un'intestazione) a 126 (scegliete un numero abbastanza grande per essere sicuri). L'operazione è semplice: come qualcosa viene cambiato sul mio foglio, il filtro sull'intervallo specificato viene rimosso/riapplicato di nuovo in modo da averlo aggiornato. Ciò che necessita un po’ di spiegazioni qui sono Field e Criteria.

Il Field è un offset intero dell'intervallo. Nel mio caso, ho solo un filtro a colonna singola e l'intervallo è costituito da una sola colonna (L) che è la prima dell'intervallo (quindi uso 1 come valore).

Il Criterio è una stringa che descrive il filtro da applicare all'intervallo di dati. Nel mio esempio, voglio mostrare solo le righe in cui la colonna L è diversa da 0 (quindi ho usato “<>0”).

Questo è tutto. Per ulteriori riferimenti sul metodo Range.AutoFilter, vedi: https://msdn.microsoft.com/en-us/library/office/ff193884.aspx

Clicca con il tasto destro sul nome del tuo foglio, scegli “Visualizza codice” e incolla il codice qui sotto. Dopo aver incollato, clicca sull'icona di Excel sotto “File” in alto a sinistra, o digita Alt-F11, per tornare alla visualizzazione del foglio di calcolo.

Questo abiliterà l'aggiornamento automatico. Non dimenticare di salvare il file in un formato con supporto per le macro: .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

Solo per consolidare le risposte:

Sorin dice:

Fai clic destro sul nome del tuo foglio, scegli “Visualizza codice” e incolla il codice qui sotto. Dopo aver incollato, fai clic sull'icona Excel sotto “File” in alto a sinistra, o digita Alt-F11, per tornare alla vista del foglio di calcolo.

Questo abiliterà l'aggiornamento automatico. Non dimenticare di salvare il file in un formato con supporto per le macro: .xlsm.

E Chris ha usato questo codice (che ho appena fatto nel 2010):

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

Se non espandi il post, vedi solo la risposta lunga! ;)

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

Spiacente, rep insufficiente per commentare. (Admins, sentitevi liberi di tagliare questo in un commento sopra.) La risposta dell'utente “danicotra” che inizia con “Anche io uso un VBA/Macro basato sull'evento Worksheet\Change, ma il mio approccio…” con ‘ prima rimuovi il filtro ’ poi applicalo di nuovo è la soluzione corretta quando si usa Excel 2007+. Tuttavia .AutoFilter.ApplyFilter non è valido in XL03 e precedenti quindi mostro il modo seguente.

Prego che i veri esperti e guru leggano il codice perché sono abbastanza sicuro che sia materiale di alta qualità. Forse l'inspiegabile conteggio di downvote su questa risposta può essere invertito quando la gente vede che cosa buona è fatta sotto.

danicotra ha usato un esempio semplificato. In realtà, puoi farlo più in generale. Assumi Con ActiveSheet per il seguente (o qualche altro oggetto foglio):

1. Salva l'intervallo dell'autofiltro. Ha colonne .AutoFilter.Filters.Count e righe (.AutoFilter.Range.Count/.AutoFilter.Filters.Count), salvate in rngAutofilter

2. Raccogliete in un array myAutofilters ciascuna delle 4 proprietà di ciascuno degli elementi del filtro automatico .AutoFilter.Filters.Count, facendo attenzione ad evitare “errori definiti dall'applicazione” quando .On o .Operator è falso. (myAutofilters verrebbe ridimensionato al numero di righe e colonne del passo 1)

3. Disattivate il filtro ma conservate le tendine con .ShowAllData

4. Per ogni elemento del filtro che era .On secondo il tuo array salvato, resetta 3 delle 4 proprietà di ogni elemento del filtro automatico .AutoFilter.Filters.Count. Ancora una volta fate attenzione ad evitare “errori definiti dall'applicazione” quando .Operator è falso, quindi per ogni elemento “i”, rngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i,2) o rngAutofilter. AutoFilter Field:=i, Criteria1:=myAutofilters(i,2), Operator:=myAutofilters(i,3), Criteria2:=myAutofilters(i,4)

Ora l'autofiltro sarà ripristinato, sullo stesso intervallo che era prima che il vostro codice iniziasse, ma con l'autofiltro aggiornato per i cambiamenti nei dati.

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