Come posso aggiornare automaticamente un filtro automatico di Excel quando i dati vengono cambiati?
Caso d'uso: Cambio il valore di una cella con un valore che è stato filtrato. Voglio vedere la riga corrente scomparire senza dover fare altro.
Come posso aggiornare automaticamente un filtro automatico di Excel quando i dati vengono cambiati?
Caso d'uso: Cambio il valore di una cella con un valore che è stato filtrato. Voglio vedere la riga corrente scomparire senza dover fare altro.
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
Soluzione trovata!
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
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
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! ;)
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):
Salva l'intervallo dell'autofiltro. Ha colonne .AutoFilter.Filters.Count e righe (.AutoFilter.Range.Count/.AutoFilter.Filters.Count), salvate in rngAutofilter
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)
Disattivate il filtro ma conservate le tendine con .ShowAllData
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
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;"")