Jak automatycznie odświeżyć filtr automatyczny w Excelu, gdy dane zostaną zmienione?

Zamiana kodu na ten wydaje się również działać (przynajmniej w Excelu 2010):

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

Odkryłem, że kiedy pracowałem z tabelami, to nie działało. Filtr nie był na arkuszu, ale na tabeli. ten kod zrobił sztuczkę

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

Znalazłem informacje tutaj: http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp

Ja również używam VBA/Macro opartego na zdarzeniu Worksheet_Change, ale moje podejście jest nieco inne… Ok, najpierw kod, a potem wyjaśnienia:

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

(użyj kombinacji klawiszy Alt+F11, aby pojawił się panel deweloperski i wklej kod do arkusza zawierającego filtr, który ma być automatycznie odświeżany).

W moim przykładzie zakładam, że mam prosty filtr na pojedynczą kolumnę (w moim przypadku L) i że mój zakres danych obejmuje wiersze od 1 (nawet jeśli zawiera nagłówek) do 126 (wybierz wystarczająco dużą liczbę, aby być pewnym). Działanie jest proste: gdy coś się zmienia na moim arkuszu, filtr na określonym zakresie zostaje usunięty/ponownie zastosowany, aby go odświeżyć. To co wymaga tutaj trochę wyjaśnienia to Pole i Criteria.

The Field is an integer offset of range. W moim przypadku, mam tylko filtr jednokolumnowy i zakres jest tworzony przez jedną kolumnę (L), która jest pierwsza w zakresie (dlatego używam 1 jako wartości).

Kryterium jest łańcuchem, który opisuje filtr, który ma być zastosowany do zakresu danych. W moim przykładzie chcę pokazać tylko wiersze, w których kolumna L jest różna od 0 (dlatego użyłem “0”).

To wszystko. Więcej informacji na temat metody Range.AutoFilter można znaleźć na stronie: https://msdn.microsoft.com/en-us/library/office/ff193884.aspx

Kliknij prawym przyciskiem myszy na nazwę swojego arkusza, wybierz “Wyświetl kod” i wklej poniższy kod. Po wklejeniu kodu kliknij ikonę Excela pod “Plik” w lewym górnym rogu lub wpisz Alt-F11, aby powrócić do widoku arkusza kalkulacyjnego.

W ten sposób włączysz automatyczne odświeżanie. Nie zapomnij zapisać pliku w formacie z obsługą makr 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

Aby skonsolidować odpowiedzi:

Sorin mówi:

Kliknij prawym przyciskiem myszy na nazwę swojego arkusza, wybierz “Wyświetl kod” i wklej poniższy kod. Po wklejeniu kliknij ikonę Excela pod “Plik” w lewym górnym rogu lub wpisz Alt-F11, aby powrócić do widoku arkusza kalkulacyjnego.

W ten sposób włączysz automatyczne odświeżanie. Nie zapomnij zapisać pliku w formacie z obsługą makr lie .xlsm.

A Chris użył tego kodu (który ja właśnie zrobiłem w 2010 roku):

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

Jeśli nie rozwiniesz posta, zobaczysz tylko długą odpowiedź! ;)

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

Przepraszamy, niewystarczająca liczba repów, aby skomentować. (Administratorzy, nie krępuj się, aby wyciąć to w komentarzu powyżej.) Odpowiedź użytkownika “danicotra” zaczynająca się od “Używam VBA/Macro opartego na zdarzeniu Worksheet_Change również, ale moje podejście…” z ‘ najpierw usuń filtr ’ następnie zastosuj go ponownie jest poprawnym rozwiązaniem, gdy używasz programu Excel 2007+. Jednak .AutoFilter.ApplyFilter jest niepoprawny w XL03 i wcześniejszych, więc pokazuję sposób poniżej.

Błagam, aby prawdziwi eksperci i guru przeczytali ten kod, bo jestem przekonany, że jest to materiał z najwyższej półki. Być może niewytłumaczalna liczba downvote na tę odpowiedź może zostać odwrócona, gdy ludzie zobaczą, jakie dobre rzeczy są robione poniżej.

danicotra użył uproszczonego przykładu. Właściwie możesz to zrobić bardziej ogólnie. Przyjmij With ActiveSheet dla następującego (lub jakiegoś innego obiektu arkusza):

1. Zapisz zakres autofiltra. Ma on kolumny .AutoFilter.Filters.Count, oraz (.AutoFilter.Range.Count/.AutoFilter.Filters.Count) wiersze, zapisane do rngAutofilter

2. Zbierz w tablicy myAutofilters każdą z 4 właściwości każdego z elementów autofiltru .AutoFilter.Filters.Count, uważając, aby uniknąć “błędów zdefiniowanych przez aplikację”, gdy .On lub .Operator jest fałszywy. (myAutofilters zostałoby reDim’d do liczby wierszy i kolumn w kroku 1)

3. Wyłącz filtr, ale zachowaj elementy rozwijane za pomocą .ShowAllData

4. Dla każdego elementu filtra, który był .On zgodnie z zapisaną tablicą, zresetuj 3 z 4 właściwości każdego z elementów autofiltru .AutoFilter.Filters.Count. Ponownie zwróć uwagę, aby uniknąć “błędów zdefiniowanych przez aplikację”, gdy .Operator jest fałszywy, więc dla każdego elementu “i”, rngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i,2) lub rngAutofilter. AutoFilter Field:=i, Criteria1:=myAutofilters(i,2), Operator:=myAutofilters(i,3), Criteria2:=myAutofilters(i,4)

Teraz autofiltr zostanie przywrócony, w tym samym zakresie, w którym był przed rozpoczęciem kodu, ale z autofiltrem zaktualizowanym o zmiany w danych.

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