Как автоматически обновлять автофильтр Excel при изменении данных?

Замена кода с этим, похоже, тоже сделает трюк (по крайней мере, в Excel 2010):

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

Я обнаружил, что когда я работал со столами, это не сработало. Фильтр был не на листе, а на таблице. Этот код выполнил трюк

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

я нашел информацию здесь: http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp

Я тоже использую VBA/Macro, основанный на событии Worksheet_Change, но мой подход немного отличается…. Хорошо, сначала код, а затем объяснения:

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

(Используйте комбинацию клавиш Alt+F11, чтобы появилась панель разработки, и вставьте код в рабочий лист, содержащий фильтр, который вы хотите автоматически обновить).

В моем примере я предполагаю наличие простого фильтра на одном столбце (L в моем случае), и что мой диапазон данных находится на строках от 1 (даже если он может содержать заголовок) до 126 (выберите число достаточно большое, чтобы быть уверенным). Операция проста: как что-то меняется на моем листе, фильтр на указанном диапазоне удаляется/применяется снова, чтобы обновить его. Что нужно немного объяснить здесь Field и Criteria.

Field - это целочисленное смещение диапазона. В моем случае, у меня есть только фильтр с одним столбцом, а диапазон сделан из одного столбца (L), который является первым в диапазоне (поэтому я использую 1 в качестве значения).

The Criteria - это строка, описывающая фильтр для применения к диапазону данных. В моем примере я хочу показать только строки, в которых столбец L отличается от 0 (поэтому я использовал “<>0”).

Это все. Подробнее о методе Range.AutoFilter смотрите: https://msdn.microsoft.com/en-us/library/office/ff193884.aspx

Щелкните правой кнопкой мыши по названию листа, выберите “Просмотр кода” и вставьте код ниже. После вставки щелкните по значку Excel под “Файлом” в левом верхнем углу или наберите Alt-F11, чтобы вернуться к просмотру электронной таблицы.

Это включит автоматическое обновление. Не забудьте сохранить файл в формате с поддержкой макросов 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

Просто чтобы консолидировать ответ(ы):

Сорин говорит:

Правильно щелкните имя листа, выберите “Код просмотра” и вставьте код ниже. После вставки щелкните значок Excel под “Файлом” в левом верхнем углу или введите Alt-F11, чтобы вернуться к просмотру электронной таблицы.

Это включит автоматическое обновление. Не забудьте сохранить файл в формате с поддержкой макросов lie.xlsm.

И Крис использовал этот код (что я только что сделал в 2010 году):

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

Если вы не развернете сообщение, вы увидите только длинный ответ! ;)

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

Простите, недостаточно комментариев. (Администраторы, не стесняйтесь вырезать это в комментарий выше.) Пользовательский ответ “danicotra”, начиная с “Я использую VBA/Macro на основе Worksheet_Change события тоже, но мой подход …” с 0x2 и ‘ сначала удалить фильтр 0x2 и ’, а затем применить его снова 0x2 и является правильным решением при использовании Excel 2007+. Однако .AutoFilter.ApplyFilter недействителен в XL03 и более ранних версиях, поэтому я покажу способ, описанный ниже.

Я прошу истинных экспертов и гуру прочитать код, потому что я уверен, что это материал с верхней полки. Возможно, необъяснимый счет даунвот на этот ответ может быть обращен вспять, когда люди увидят, какие хорошие вещи делаются ниже.

danicotra использовали упрощенный пример. На самом деле, вы можете сделать это в более общем виде. Assume With ActiveSheet для следующего (или другого объекта листа):

1. Сохраните диапазон автофильтра. Он имеет .AutoFilter.Filters.Count столбцы, и (.AutoFilter.Range.Count/.AutoFilter.Filters.Count) строки, сохраненные в rngAutofilter

2. Соберите в массиве myAutofilters каждое из 4 свойств каждого из .AutoFilter.Filters.Count autofilter.Items, заботясь о том, чтобы избежать “Ошибок, определенных приложением”, когда .On или .Operator ложны. (MyAutofilters был бы повторен на количество строк и столбцов в шаге 1)

3. Выключите фильтр, но сохраните выпадающие строки с помощью .ShowAllData

4. Для каждого элемента фильтра, который был .On в соответствии с сохраненным массивом, сбросьте 3 из 4 свойств каждого из .AutoFilter.Filters.Count autofilter.itemss. Снова позаботьтесь о том, чтобы избежать “Ошибок, определенных приложением”, когда .Operator равен false, поэтому для каждого элемента “i”, rngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i,2) или rngAutofilter. AutoFilter Field:=i, Criteria1:=myAutofilters(i,2), Operator:=myAutofilters(i,3), Criteria2:=myAutofilters(i,4)

Теперь автофильтр будет переустановлен, в том же диапазоне, что и до запуска вашего кода, но с обновлением автофильтра для изменения данных.

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