Hoe kan ik een Excel auto-filter automatisch vernieuwen wanneer gegevens worden gewijzigd?

De code verwisselen met deze lijkt ook te werken (althans in Excel 2010):

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

Ik ontdekte dat wanneer ik met tabellen werkte, dit niet werkte. Het filter stond niet op het blad maar op de tabel. Deze code deed de truc

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

Ik vond de informatie hier: http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp

Ik gebruik ook een VBA/Macro gebaseerd op Worksheet_Change event, maar mijn aanpak is iets anders… Ok, eerst de code en dan de uitleg:

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

(Gebruik de toetsencombinatie Alt+F11 om het ontwikkelpaneel te laten verschijnen en plak de code in het werkblad met het filter dat je automatisch wilt laten verversen).

In mijn voorbeeld ga ik ervan uit dat ik een eenvoudig filter op een enkele kolom heb (L in mijn geval) en dat mijn gegevensbereik op rijen van 1 (ook al kan er een koptekst in voorkomen) tot 126 ligt (kies een getal dat groot genoeg is om zeker te zijn). De werking is eenvoudig: als er iets wordt gewijzigd op mijn blad, wordt het filter op het gespecificeerde bereik verwijderd/toegepast om het te verversen. Wat hier een beetje uitleg behoeft zijn Field en Criteria.

Het Veld is een geheel getal dat het bereik aangeeft. In mijn geval heb ik slechts een filter met één kolom en het bereik wordt gevormd door één kolom (L) die de eerste in het bereik is (daarom gebruik ik 1 als waarde).

De Criteria is een tekenreeks die beschrijft welk filter op het gegevensbereik moet worden toegepast. In mijn voorbeeld wil ik alleen rijen tonen waar kolom L verschilt van 0 (daarom heb ik “>0” gebruikt).

Dat is alles. Voor meer informatie over Range.AutoFilter zie: https://msdn.microsoft.com/en-us/library/office/ff193884.aspx

Klik met de rechtermuisknop op de naam van uw werkblad, kies “Code weergeven” en plak de onderstaande code. Klik na het plakken op het Excel-icoontje onder “Bestand” linksboven, of typ Alt-F11, om terug te keren naar de spreadsheet-weergave.

Dit schakelt automatisch vernieuwen in. Vergeet niet het bestand op te slaan in een formaat met macro-ondersteuning leugen .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

Om de antwoorden te consolideren:

Sorin zegt:

Klik met de rechtermuisknop op uw bladnaam, kies “Code weergeven” en plak de onderstaande code. Klik na het plakken op het Excel-icoontje onder “Bestand” linksboven, of typ Alt-F11, om terug te keren naar de spreadsheet-weergave.

Hiermee wordt automatisch vernieuwen ingeschakeld. Vergeet niet om het bestand op te slaan in een formaat met macro-ondersteuning leugen .xlsm.

En Chris gebruikte deze code (die ik net in 2010 heb gedaan):

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

Als je de post niet uitbreidt, zie je alleen het lange antwoord! ;)

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

Sorry, te weinig reacties om commentaar te geven. (Admins, voel je vrij om dit in een commentaar hierboven te knippen.) Reactie van gebruiker “danicotra” beginnend met “Ik gebruik ook een VBA/Macro gebaseerd op Worksheet_Change event, maar mijn aanpak…” met ‘ eerst filter verwijderen ’ dan weer toepassen is de juiste oplossing bij gebruik van Excel 2007+. Maar .AutoFilter.ApplyFilter is ongeldig in XL03 en eerder, dus ik laat de onderstaande manier zien.

Ik smeek dat echte experts en goeroes de code lezen want ik ben er vrij zeker van dat het materiaal van de bovenste plank is. Misschien kan het onverklaarbare aantal downvote’s op dit antwoord worden teruggedraaid als mensen zien wat voor goed spul hieronder wordt gedaan.

danicotra gebruikte een vereenvoudigd voorbeeld. Eigenlijk kun je dit algemener doen. Ga uit van Met ActiveSheet voor het volgende (of een ander sheetobject):

1. Sla het bereik van het autofilter op. Het heeft .AutoFilter.Filters.Count kolommen, en (.AutoFilter.Range.Count/.AutoFilter.Filters.Count) rijen, opgeslagen in rngAutofilter

2. Verzamel in een array myAutofilters elk van de 4 eigenschappen van elk van de .AutoFilter.Filters.Count autofilter items, waarbij u ervoor moet zorgen dat u “Application-defined errors” vermijdt wanneer .On of .Operator false is. (mijnAutofilters zou worden geDim’d naar het aantal rijen en kolommen in stap 1)

3. Schakel het filter uit maar behoud de uitklapmenu’s met .ShowAllData

4. Voor elk filteritem dat .On was volgens uw opgeslagen matrix, reset u 3 van de 4 eigenschappen van elk van de .AutoFilter.Filters.Count autofilteritems. Zorg er opnieuw voor dat u “Toepassingsgedefinieerde fouten” vermijdt wanneer .Operator false is, dus voor elk item “i”, rngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i,2) of rngAutofilter. AutoFilter Field:=i, Criteria1:=myAutofilters(i,2), Operator:=myAutofilters(i,3), Criteria2:=myAutofilters(i,4)

Nu wordt het autofilter opnieuw ingesteld, over hetzelfde bereik als het was voordat uw code begon, maar met het autofilter bijgewerkt voor wijzigingen in de gegevens.

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