Как показать отфильтрованные столбцы в строке состояния в Excel

Что делает макрос: Когда у вас есть большая таблица с множеством столбцов, которые не отфильтрованы, иногда трудно сказать, какие столбцы фильтруются, а какие — нет. Конечно, можно прокручивать столбцы, всматриваясь в каждый выпадающий список Автофильтра, но это очень долго.Этот макрос помогает конкретно показать все фильтры в строке состояния.
Панель состояния находится в нижней части окна Excel.

Как макрос работает

Этот макрос перебирает поля в наборе данных AutoFiltered. После того, как цикл завершен, мы проходим заключительную строку к свойству StatusBar.

Код макроса

Sub PokazatOtfiltrovannieStolbciVStrokeSostoyaniya()
'Шаг 1: объявляем переменные
Dim AF As AutoFilter
Dim TargetField As String
Dim strOutput As String
Dim i As Integer
'Шаг 2: Проверить, если AutoFilter существует, если нет - Выход
If ActiveSheet.AutoFilterMode = False Then
Application.StatusBar = False
Exit Sub
End If
'Шаг 3: Установить Автофильтр и начать цикл
Set AF = ActiveSheet.AutoFilter
For i = 1 To AF.Filters.Count
'Шаг 4: Захватить отфильтрованные имена полей
If AF.Filters(i).On Then
TargetField = AF.Range.Cells(1, i).Value
strOutput = strOutput & " | " & TargetField
End If
Next
'Шаг 5: Показать фильтры, если они есть
If strOutput = "" Then
Application.StatusBar = False
Else
Application.StatusBar = "ДАННЫЕ ОТФИЛЬТРИРОВАНЫ ПО " & strOutput
End If
End Sub

Как это код работает

1. Шаг 1 объявляет четыре переменные. AF является переменной автофильтр, который используется для манипулирования объектом AutoFilter. TargetField переменная строка, которую мы используем, чтобы держать имена полей любого поля, которое фактически фильтруют. strOutput переменная строка, которую мы используем для построения окончательного текста, который идет в строке состояния. И, наконец, переменная i служит как простой счетчик, что позволяет нам выполнять итерацию по полям в нашем Автофильтре.
2. Шаг 2 проверяет свойство AutoFilterMode, чтобы увидеть, применяет ли лист автофильтры. Если нет, то мы устанавливаем свойство StatusBar в значение False. Это имеет эффект очистки строки состояния. Затем мы выходим из процедуры.
3. Шаг 3 устанавливает переменную AF к Автофильтру на активном листе. Затем мы устанавливаем наш счетчик от 1 до максимального количества столбцов в диапазоне AutoFiltered. Объект AutoFilter отслеживает столбцы с номерами индексов. Столбец 1 индекс 1; столбец 2 является индексом 2 и т.д. Идея заключается в том, что цикл проходит каждый столбец в
   Автофильтре, используя переменную (I), как номер индекса.
4. Шаг 4 проверяет статус AF.Filters объекта для каждого (I) — порядковый номер столбца. Если Автофильтр фильтрует столбец, то статус этого столбца Вкл. Если столбец отфильтрован, то захватываем имя поля в TargetField. Мы на самом деле можем получить имя поля с помощью ссылки на диапазон нашего объекта AF Автофильтр. С помощью этого диапазона, мы можем использовать элемент ячейки, чтобы определить имя поля.
   Ячейка (1,1) фиксирует значение в первой строке, первом столбце. Ячейка (1,2) фиксирует значение в первой строке, втором столбце и т.д.
   Как видите на шаге 4 закодирована строка 1 и используется переменная (I), чтобы указать индекс столбца. Это означает, что, как Макрос проводит итерацию через столбцы, она всегда захватывает значение в строке один в качестве имени TargetField .
   После того, как у нас есть имя TargetField , мы можем передать эту информацию (strOutput в нашем случае). strOutput просто хранит имена находит и сцепляет их в удобном виде для чтения текстовой строки.
5. Шаг 5 сначала проверяет, есть ли что-то в строке strOutput. Если
   strOutput пуст, это означает, что макрос не нашел ни одного столбца в нашем Автофильтре. В этом случае Шаг 5 просто устанавливает свойство StatusBar False, передавая управление обратно Excel. Если strOutput не пуст, шаг 5 устанавливает свойство StatusBar равным некоторому вспомогательному тексту вместе с нашей строкой strOutput.

Как использовать

Вы хотите в идеале, чтобы этот макрос запускался каждый раз, когда поле фильтруется.
Однако Excel не имеет событие OnAutoFilter. Ближе всего к этому является событие Worksheet_Calculate. Автофильтры сами по себе на самом деле не вычисляют ничего, так что вам нужно ввести «непостоянную» функцию на листе, которая содержит ваши AutoFiltered данные. Непостоянная функция та, которая делает перерасчет при внесении любых изменений на рабочем листе.
Можно использовать функцию Now (). Функция Now является непостоянной функцией, которая возвращает дату и время. При этом на листе обязательно нужно пересчитывать каждый раз, когда AutoFilter изменяется.
Поместите Now в любом месте на листе (введя = Now () в любой клетке). Затем скопируйте и вставьте макрос в окно Worksheet_Calculate код события

1. Активируйте редактор Visual Basic, нажав ALT + F11 на клавиатуре.
2. В окне проекта, найти свой проект / имя рабочей книги и нажмите на знак плюс
   рядом с ней, чтобы увидеть все листы.
3. Нажмите на лист, из которого вы хотите, чтобы вызвать код.
4. Выберите событие Calculate из событий в раскрывающемся списке.
5. Введите или вставьте код.

Private Sub Worksheet_Deactivate()
Application.StatusBar = False
End Sub
Private Sub Worksheet_Activate()
Call Worksheet_Calculate
End Sub

Кроме того, можно добавить этот кусок кода в книгу BeforeClose :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.StatusBar = False
End Sub

Событие Worksheet_Deactivate очищает строку состояния при переходе на другой лист или книги. Это позволит избежать путаницы при перемещении между листами.
Событие Worksheet_Activate запускает макрос в Worksheet_Calculate и возвращает показатели Строки состояния при переходе обратно к отфильтрованной листа.
Событие Workbook_BeforeClose очищает строку состояния при закрытии книги. Это позволит избежать путаницы при перемещении между книгами.