Vba excel убрать автофильтр

Обновлено: 07.07.2024

Hey this works great but I'd like to re-apply the autofilter to multiple sheets if possible.
I have 3 sheets (Enquiry, Booked, No sale)

What I'm trying to do is essentially move the data when I change the item status code:
"Enquiry" is filtered to show 'In progress' only, "Booked" is filtered to show 'Booked' only etc

Is there a way to re-apply filter to multiple worksheets when making a change on "enquiry"

I actually have data from an other Excel file that got imported in a Excelsheet with the name "Database". Then I import this data in the same Excel file but in an other ExcelSheet "Overview". I want when the data changes in the orgininal source, that the filter applies in the sheet "Overview". Thank you in forward for the one who can help me :). P.S. cant use VBA in the firt excelsheet

This is a great bit of code thank you. The only issue I am having is I'm using a drop down on a separate chart sheet. If I manually change the value in the cell associated with the drop down, it works. But when I try to just use the drop down, it won't update. Any thoughts?

Hi, thanks so much for the help. Something isn't working right for me. Here's the story.

Sheet1 has variable data. Sheet3 has static data and filter. Filter criteria on "Sheet3" comes from Sheet1. Sheet1 has data that comes from filtered results on Sheet3.

Sheet3 has code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1:U14").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("A22:U23"), CopyToRange:=Range("A25:U26"), Unique:=False
End Sub

It works great if I do anything on Sheet3. No problems. Thank you!

At first I had code on Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet3").AutoFilter.ApplyFilter
End Sub

Which resulted in the error "Runtime error 91, Object Variable or With Block not Set".

I changed the code based on comments to be:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Sheets("Sheet3").AutoFilter.ApplyFilter
End Sub

Now I don't get an error, but the data on Sheet3 and therefore Sheet1 don't change. In other words, the event of applying the filter to Sheet3 doesn't occur when I make a change on Sheet1. It doesn't matter if I hit <return> or click on another cell after changing the Sheet3 filter criteria cell that is set on Sheet1.

As an aside, I expect that if I wanted to have multiple cells on Sheet1 that caused filters on Sheets 4 and 5 in addition to Sheet3, I would need the code on Sheet 1 to read:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Sheets("Sheet3").AutoFilter.ApplyFilter
Sheets("Sheet4").AutoFilter.ApplyFilter
Sheets("Sheet5").AutoFilter.ApplyFilter
End Sub

Cant get this to work at all on office 365
any suggestions

This code works great, thanks a lot.

I, however, have one small issue with it - if I change values in any cell that is not part of the table, I am presented with Runtime error saying:

"Run-time error '91':

Object variable or With block variable not set up"

I have options to Debug or End, option to Continue is greyed out. I can click on "End" and the code still works, however it is very annoying having to deal with this popup window after every change.

Anybody has similar experience or a suggestion about how to sort this?

Hello, David,
To solve your problem, you may apply the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Sheets("Sheet3").AutoFilter.ApplyFilter
End Sub

Please try it, hope it can help you!

I have implemented your solution and it is indeed fixed.

Hello I have had the same issue, pasted the new code and change the name of the sheet but then nothing happens, the filter doesn't update

Hi, this seems to work great but I am having problems when there are more than one filter on the same worksheet (tab). I converted the range of cells to a table to allow separate and multiple filters within the same worksheet. This example only appears to update one of the tables/filters. Any suggestions on how to update ALL tables/filters within a worksheet?

Hi, Tom,
The code in this article works well for multiple tables within a worksheet, you just need to press Enter key after changing the data instead of click to other cell.
Please try it.

Hi, that works great, however only when manually changing data in the table.

I have a ‘top ten/leader board’ style filtered table which is populated from data entry on a separate worksheet (actually the data goes through 3 worksheets before getting to the table). When the data is changed in the data entry worksheet the leader board table figures updates however the filter doesn’t auto refresh.
Any ideas on how to do that?
Much Obliged.
Alex

This seems great. Can you tell me how to do the same for Sort, rather than Filter, please?

Hello, Chris,
May be, the following article can solve your problem, please view:

works like a champ, and so simple. thank you very much!

This solution works perfectly. Thanks for writing it up! If anyone is having trouble, there are a few things to consider.

First, the Worksheet_Change event is called on a sheet-by-sheet basis. This means if you have multiple sheets which have filters you need updated, you will need to respond to all those events. One Worksheet_Change subroutine for each worksheet, not one subroutine for the entire workbook (one exception - see note below).

Second, and a follow-on to the first, the code must be placed in the code module specific to the worksheet to be monitored. Its easy to (inadvertently) switch code modules once you get into the VB editor, so care must be taken to place it specific to the sheet you want to monitor for data changes.

Third, this is unconfirmed, but possibly a point of error. The example uses sheet names of "Sheet1", "Sheet2", etc. If you've renamed the sheets, you may need to update the code. Note in the example, Sheet7 has been given the name "dfdf". If you wanted to update the filter there, you'd need to use;
Sheets("dfdf").AutoFilter.ApplyFilter
not;
Sheets("Sheet7").AutoFilter.ApplyFilter

It might be good to update the article including an example with a renamed sheet.

Finally, if you want to monitor one sheet for data changes, but update filters on multiple sheets, then you only need one subroutine, placed in the code module of the worksheet you are monitoring. The code will look something like this;

Итог: научиться создавать макросы, использовать фильтры на диапазоны и таблицы с помощью метода AutoFilter VBA. Статья содержит ссылки на примеры для фильтрации различных типов данных, включая текст, цифры, даты, цвета и значки.

Уровень мастерства: средний

Файл Excel, содержащий код, можно скачать ниже. Этот файл содержит код для фильтрации различных типов данных и типов фильтров.

VBA AutoFilters Guide.xlsm (100.5 KB)

Написание макросов для фильтров

Фильтры являются отличным инструментом для анализа данных в Excel. Для большинства аналитиков и частых пользователей Excel фильтры являются частью нашей повседневной жизни. Мы используем раскрывающиеся меню фильтров для применения фильтров к отдельным столбцам в наборе данных. Это помогает нам связывать цифры с отчетами и проводить исследование наших данных.

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

В этой статье объясняется, как создавать макросы для автоматизации процесса фильтрации. Это обширное руководство по методу автофильтра в VBA.

У меня также есть статьи с примерами для различных фильтров и типов данных, в том числе: пробелы, текст, числа, даты, цвета и значки, и очищающие фильтры.

Мы можем легко получить код VBA для фильтров, включив макро-рекордер, а затем применив один или несколько фильтров к диапазону / таблице.

Вот шаги для создания макроса фильтра с помощью устройства записи макросов:

Включите рекордер макросов:
1. Вкладка «Разработчик»> «Запись макроса».
2. Дайте макросу имя, выберите, где вы хотите сохранить код, и нажмите ОК.
Если вы уже использовали макрос-рекордер для этого процесса, то вы знаете, насколько он может быть полезен. Тем более, что наши критерии фильтрации становятся более сложными.

Код будет выглядеть примерно так:

Мы видим, что каждая строка использует метод AutoFilter для применения фильтра к столбцу. Он также содержит информацию о критериях фильтра.

Мы видим, что каждая строка использует метод AutoFilter для применения фильтра к столбцу. Он также содержит информацию о критериях фильтра.

Метод автофильтрации

Метод AutoFilter используется для очистки и применения фильтров к одному столбцу в диапазоне или таблице в VBA. Он автоматизирует процесс применения фильтров через выпадающие меню фильтров и делает, чтобы все работало.

Его можно использовать для применения фильтров к нескольким столбцам путем написания нескольких строк кода, по одной для каждого столбца. Мы также можем использовать Автофильтр, чтобы применить несколько критериев фильтрации к одному столбцу, так же, как в выпадающем меню фильтра, установив несколько флажков или указав диапазон дат.

Написание кода автофильтра

Вот пошаговые инструкции по написанию строки кода для автофильтра.

Шаг 1: Ссылка на диапазон или таблицу

Метод AutoFilter является частью объекта Range. Поэтому мы должны ссылаться на диапазон или таблицу, к которым применяются фильтры на листе. Это будет весь диапазон, к которому применяются фильтры.

Следующие примеры включают / отключают фильтры в диапазоне B3: G1000 на листе автофильтра.

Вот пример использования таблиц Excel.

Метод AutoFilter имеет 5 необязательных параметров, которые мы рассмотрим далее. Если мы не укажем ни один из параметров, как в приведенных выше примерах, метод AutoFilter включит / выключит фильтры для указанного диапазона. Это переключение. Если фильтры включены, они будут выключены, и наоборот.

Диапазоны или таблицы?

Фильтры работают одинаково как для обычных диапазонов, так и для таблиц Excel.

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

5 (или 6) параметров автофильтра

Метод AutoFilter имеет 5 (или 6) необязательных параметров, которые используются для указания критериев фильтрации для столбца. Вот список параметров.

Мы можем использовать комбинацию этих параметров, чтобы применять различные критерии фильтрации для разных типов данных. Первые четыре являются наиболее важными, поэтому давайте посмотрим, как их применять.

Шаг 2: Параметр поля

В приведенном ниже примере поле 4 является столбцом «Продукт», поскольку это 4-й столбец в диапазоне фильтра / таблице.

Фильтр столбца очищается, когда мы указываем только параметр Field, а другие критерии отсутствуют.

Мы также можем использовать переменную для параметра Field и установить ее динамически. Я объясню это более подробно ниже.

Шаг 3: Параметры критериев

Существует два параметра, которые можно использовать для указания фильтра Критерии, Criteria1 и Criteria2 . Мы используем комбинацию этих параметров и параметра Operator для разных типов фильтров. Здесь все становится сложнее, поэтому давайте начнем с простого примера.

Это то же самое, что выбрать один элемент из списка флажков в раскрывающемся меню фильтра.

Общие правила для Criteria1 и Criteria2

Значения, которые мы указываем для Criteria1 и Criteria2, могут быть хитрыми. Вот несколько общих рекомендаций о том, как ссылаться на значения параметра Criteria.
- Значением критерия является строка, заключенная в кавычки. Есть несколько исключений, когда критерии являются постоянными для периода времени даты и выше / ниже среднего.
- При указании фильтров для отдельных чисел или дат форматирование чисел должно соответствовать форматированию чисел, применяемому в диапазоне / таблице.
- Оператор сравнения больше / меньше чем также включен в кавычки перед числом.
- Кавычки также используются для фильтров для пробелов «=» и не пробелов «<>».
Шаг 4: Параметр оператора

Что если мы хотим выбрать несколько элементов из раскрывающегося списка фильтров? Или сделать фильтр для диапазона дат или чисел?

Для этого нам нужен Operator . Параметр Operator используется для указания типа фильтра, который мы хотим применить. Он может варьироваться в зависимости от типа данных в столбце. Для
Operator должна использоваться одна из следующих 11 констант.

Вот ссылка на страницу справки MSDN, которая содержит список констант для перечисления XlAutoFilterOperator.

Operator используется в сочетании с Criteria1 и / или Criteria2, в зависимости от типа данных и типа фильтра. Вот несколько примеров.

Это основы написания строки кода для метода AutoFilter. Будет сложнее с различными типами данных.

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

Автофильтр не является дополнением

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

Это означает, что это не дополнение. Следующие 2 строки НЕ создадут фильтр для Продукта 1 и Продукта 2. После запуска макроса столбец Продукт будет отфильтрован только для Продукта 2.

Если вы хотите применить фильтр с несколькими критериями к одному столбцу, вы можете указать это с помощью параметров
Criteria и Operator .

Как установить номер поля динамически

Если мы добавим / удалим / переместим столбцы в диапазоне фильтра, то номер поля для отфильтрованного столбца может измениться. Поэтому я стараюсь по возможности избегать жесткого кодирования числа для параметра Field.

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

Номер столбца будет найден при каждом запуске макроса. Нам не нужно беспокоиться об изменении номера поля при перемещении столбца. Это экономит время и предотвращает ошибки (беспроигрышный вариант)!

Используйте таблицы Excel с фильтрами

Использование таблиц Excel дает множество преимуществ, особенно при использовании метода автофильтрации. Вот несколько основных причин, по которым я предпочитаю таблицы.
- Нам не нужно переопределять диапазон в VBA, поскольку диапазон данных изменяет размер (строки / столбцы добавляются / удаляются). На всю таблицу ссылается объект ListObject.
- Данные в таблице легко ссылаться после применения фильтров. Мы можем использовать свойство DataBodyRange для ссылки на видимые строки для копирования / вставки, форматирования, изменения значений и т.д.
- Мы можем иметь несколько таблиц на одном листе и, следовательно, несколько диапазонов фильтров. С обычными диапазонами у нас может быть только один отфильтрованный диапазон на лист.
- Код для очистки всех фильтров в таблице легче написать.
Фильтры и типы данных

Параметры раскрывающегося меню фильтра изменяются в зависимости от типа данных в столбце. У нас есть разные фильтры для текста, чисел, дат и цветов. Это создает МНОГО различных комбинаций операторов и критериев для каждого типа фильтра.

Я создал отдельные посты для каждого из этих типов фильтров. Посты содержат пояснения и примеры кода VBA.

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

Почему метод автофильтрации такой сложный?

Этот пост был вдохновлен вопросом от Криса, участника The VBA Pro Course. Комбинации Критерии и Операторы могут быть запутанными и сложными. Почему это?

Ну, фильтры развивались на протяжении многих лет. Мы увидели много новых типов фильтров, представленных в Excel 2010, и эта функция продолжает улучшаться. Однако параметры метода автофильтра не изменились. Они отлично подходят для совместимости со старыми версиями, но также означает, что новые типы фильтров работают с существующими параметрами.

Большая часть кода фильтра имеет смысл, но сначала может быть сложно разобраться. К счастью, у нас есть макро рекордер, чтобы помочь с этим.

Я надеюсь, что вы можете использовать эту статью и файл Excel в качестве руководства по написанию макросов для фильтров. Автоматизация фильтров может сэкономить нам и нашим пользователям массу времени, особенно при использовании этих методов в более крупном проекте автоматизации данных.

Итог: узнайте, как применять фильтры для пустых ячеек с VBA. Включает примеры фильтрации пустых и непустых ячеек с использованием метода автофильтра.

Уровень мастерства: Средний

Файл Excel, содержащий код, можно скачать ниже. Этот файл содержит код для фильтрации различных типов данных и типов фильтров. Пожалуйста, ознакомьтесь с моей статьей Фильтрация сводной таблицы или среза по самой последней дате или периоду для более подробной информации.

VBA AutoFilters Guide.xlsm (100.5 KB)

Фильтр для пустых ячеек

Мы также можем отфильтровать ячейки без пропусков, сняв флажок (Пробелы).

Следующий макрос содержит примеры фильтрации пустых и непустых ячеек (исключая пустые). Пожалуйста, ознакомьтесь с моей статьей Фильтрация сводной таблицы или среза по самой последней дате или периоду для получения более подробной информации о том, как использовать метод AutoFilter и его параметры.

Образцы кода VBA для текстовых фильтров

Код в поле ниже можно скопировать / вставить в VB Editor.

Фильтры и типы данных

Параметры раскрывающегося меню фильтра изменяются в зависимости от типа данных в столбце. У нас есть разные фильтры для текста, чисел, дат и цветов. Это создает МНОГО различных комбинаций операторов и критериев для каждого типа фильтра.

Я создал отдельные статьи для каждого из этих типов фильтров. Статьи содержат пояснения и примеры кода VBA.
- Как очистить фильтры с помощью VBA
- Как фильтровать текст с помощью VBA
- Как фильтровать числа с помощью VBA
- Как отфильтровать даты по VBA
- Как отфильтровать цвета и значки с помощью VBA
Файл в разделе загрузок выше содержит все эти примеры кода в одном месте. Вы можете добавить его в свою личную книгу макросов и использовать макросы в своих проектах.

Пожалуйста, оставьте комментарий ниже с любыми вопросами или предложениями. Спасибо!

Кажется, старые макросы не работают. У меня есть правильный набор безопасности для запуска макросов VBA, но когда я попробовал несколько методов для очистки ВСЕХ фильтров на листе, я получаю ошибку компиляции.

Вот что я пробовал:

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

23 ответа

Если на листе уже есть фильтр, то:

ShowAllData выдаст ошибку, если фильтр в данный момент не применяется. Это будет работать:

Для таблиц попробуйте это, чтобы проверить, включен ли он и выключен:

Чтобы снова включить:

это хорошо работает.!

Я нашел, что этот обходной путь работает довольно эффективно. Он в основном удаляет автофильтр из таблицы, а затем повторно применяет его, удаляя все предыдущие фильтры. По моему опыту, это не склонно к обработке ошибок, которая требуется для других методов, упомянутых здесь.

Это великолепно, единственный ответ, который я нашел, который удовлетворял мою особую потребность, большое спасибо за то, что подняли его!

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

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

Надеюсь, это поможет кому-то там:)

Я обычно использую этот код

Это тоже будет работать:

Сначала проверяется, установлен ли AutoFilterMode (возможна ли фильтрация), затем проверяется, включен ли FilterMode (выполняется ли фильтрация по чему-либо), затем отключается фильтрация.

Относительно ошибок, то есть защиты - другие ответы

Контекст добавлен (мой скрипт зацикливается на листах, которые затем сохраняются как CSV, поэтому необходимо удалить фильтры - но оставьте AutoFilterMode включенным, если он установлен:

В Excel есть два типа фильтров:

Функция автоматического фильтра позволяет фильтровать из интерфейса Excel с помощью этих крошечных выпадающих кнопок. А функция расширенного фильтра позволяет фильтровать с использованием диапазона критериев.

Метод ShowAll удаляет фильтры, как, например, показывает все строки, но не избавляется от этих выпадающих кнопок. Чтобы удалить эти кнопки, необходимо установить для свойства AutoFilterMode рабочего листа значение FALSE.

Вот Sub, который я часто использую для удаления фильтров:

Показывает все данные и удаляет выпадающие кнопки. Это удобно при укладке (копировании и вставке) данных из нескольких листов или рабочих книг. Надеюсь, это поможет.

Попробуйте что-то вроде этого:

.FilterMode возвращает значение true, если лист находится в режиме фильтра. ( Подробнее см. .)
См. это для получения дополнительной информации о .AutoFilter
. И, наконец, этот предоставит дополнительную информацию о .ShowAllData метод.

Вот одна строчка, которую я использую. Он проверяет автофильтр и, если он найден, удаляет его.

Просто активируйте заголовки фильтра и запустите showalldata, работает на 100%. Что-то вроде:

Если у вас есть заголовки полей в A1: Z1 и R1: Y1 соответственно.

Я использую .filtermode , если фильтр включен, возвращает true

Этот код очищает все фильтры и удаляет сортировку.

Вот код для исправления фильтров. Например, если вы включаете фильтры на листе, а затем добавляете столбец, тогда вы хотите, чтобы новый столбец также был покрыт фильтром.

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

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

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

Все, что вам нужно, это:

Почему? Как и лист, AutoFilter также имеет метод ShowAllData, но он не выдает ошибку, даже если автофильтр включен без активного фильтра.

Читайте также: