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

Question

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

Вариант использования: я изменяю значение одной ячейки на значение, которое было отфильтровано. Я хочу, чтобы текущая строка исчезла без необходимости делать что-либо еще.

Answer 1

Обмен кода с этим, похоже, тоже помогает (по крайней мере, в Excel 2010):

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

Answer 2

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

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

Answer 3

Я также использую 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 (выберите число, достаточно большое, чтобы конечно). Операция проста: когда что-то меняется на моем листе, фильтр в указанном диапазоне снова удаляется / повторно применяется, чтобы его обновить. Что нужно немного объяснить здесь, это поле и критерии.

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

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

Это все. Для получения дополнительной информации о диапазоне.Метод автофильтрации: см. Https://msdn.microsoft.com/en-us/library/office/ff193884.aspx.

Answer 4

Щелкните правой кнопкой мыши на имени вашего листа, выберите "Просмотр кода" и вставьте код ниже. После вставки щелкните значок 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

Answer 5

Просто чтобы закрепить ответ (ы):

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

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

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

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

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

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

Answer 6

Извините, недостаточно комментариев для комментирования. (Администраторы, не стесняйтесь вырезать это в комментарии выше.) Ответ пользователя "danicotra", начинающийся с "Я использую VBA/Macro на основе события Worksheet_Change, но мой подход ..." с
'сначала удалите фильтр
затем примените его снова
является правильным решением при использовании Excel 2007+. Тем не мение .Автофильтр.ApplyFilter недействителен в XL03 и ранее, поэтому я покажу путь ниже.

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

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

1. Сохраните диапазон автофильтра. Она имеет .Автофильтр.Фильтры.Подсчитать столбцы и (.Автофильтр.Спектр.Граф /.Автофильтр.Фильтры.Количество) строк, сохраненных в rngAutofilter

2. Соберите в массив myAutofilters каждое из 4 свойств каждого из.Автофильтр.Фильтры.Подсчитайте элементы автофильтра, стараясь при этом избежать ошибок, определенных приложением.На или.Оператор ложный. (myAutofilters будет reDim'd на количество строк и столбцов в шаге 1)

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

4. Для каждого элемента фильтра, который был.В соответствии с сохраненным массивом сбросьте 3 из 4 свойств каждого из.Автофильтр.Фильтры.Подсчитать элементы автофильтра. Снова позаботьтесь о том, чтобы избежать «ошибок, определенных приложением», когда.Оператор ложный, поэтому для каждого элемента "я",
   rngAutofilter.Поле автофильтра:= i, критерии1:= myAutofilters (i, 2)
   или же
   rngAutofilter.Поле автофильтра:= i, критерии1:= myAutofilters (i, 2), оператор:= myAutofilters (i, 3), критерии2:= 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