Vba excel отключить автофильтр

Обновлено: 07.07.2024

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

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

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

ОТВЕТЫ

Ответ 1

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

Ответ 2

Ответ 3

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

Ответ 4

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

Ответ 5

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

Ответ 6

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

Ответ 7

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

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

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

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

Ответ 8

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

Ответ 9

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

Ответ 10

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

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

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

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

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

Ответ 11

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

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

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

Ответ 12

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

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

Ответ 13

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

Ответ 14

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

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

Ответ 15

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

Ответ 16

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

Ответ 17

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

Ответ 18

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

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

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

Ответ 19

Все, что тебе нужно:

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

Ответ 20

Это будет сброшено, только если у вас есть фильтр, и не вызовет никаких ошибок, когда нет никакого фильтра. Если ActiveSheet.AutoFilterMode, то ActiveSheet.Columns("A"). Автофильтр

Ответ 21

Это работает лучше всего для меня.

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

Ответ 22

Я использую этот подход для нескольких таблиц и диапазонов как уникальный способ.

Итог: научиться создавать макросы, использовать фильтры на диапазоны и таблицы с помощью метода 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 в качестве руководства по написанию макросов для фильтров. Автоматизация фильтров может сэкономить нам и нашим пользователям массу времени, особенно при использовании этих методов в более крупном проекте автоматизации данных.

Многие функции Excel также доступны для использования в VBA - и Автофильтр метод - одна из таких функций.

Если у вас есть набор данных, и вы хотите отфильтровать его по критерию, вы можете легко сделать это с помощью параметра «Фильтр» на ленте «Данные».

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

Тогда зачем даже использовать автофильтр в VBA?

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

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

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

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

В таком сценарии использование VBA Autofilter может ускорить работу и сэкономить время.

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

Синтаксис автофильтра Excel VBA
- Выражение: Это диапазон, к которому вы хотите применить автоматический фильтр.
- Поле: [Необязательный аргумент] Это номер столбца, который вы хотите отфильтровать. Это считается слева в наборе данных. Поэтому, если вы хотите отфильтровать данные на основе второго столбца, это значение будет 2.
- Критерии1: [Необязательный аргумент] Это критерии, на основе которых вы хотите отфильтровать набор данных.
- Оператор: [Необязательный аргумент] Если вы также используете критерий 2, вы можете объединить эти два критерия на основе Оператора. Для использования доступны следующие операторы: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
- Критерии2: [Необязательный аргумент] Это второй критерий, по которому вы можете фильтровать набор данных.
- VisibleDropDown: [Необязательный аргумент] Вы можете указать, хотите ли вы, чтобы раскрывающийся значок фильтра отображался в отфильтрованных столбцах или нет. Этот аргумент может иметь значение ИСТИНА или ЛОЖЬ.
Все остальные аргументы, кроме Expression, необязательны.

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

Приведенный выше код просто применит метод автофильтра к столбцам (или, если он уже применен, удалит его).

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

Если у вас есть отфильтрованные данные, он удалит фильтры и покажет вам полный набор данных.

Теперь давайте посмотрим на несколько примеров использования Excel VBA Autofilter, которые прояснят его использование.

Пример: фильтрация данных на основе текстового условия

Предположим, у вас есть набор данных, показанный ниже, и вы хотите отфильтровать его на основе столбца «Элемент».

В приведенном ниже коде будут отфильтрованы все строки, в которых элементом является «Принтер».

Приведенный выше код относится к Sheet1, а внутри него - к A1 (которая является ячейкой в наборе данных).

Обратите внимание, что здесь мы использовали Field: = 2, поскольку столбец элемента - это второй столбец в нашем наборе данных слева.

Теперь, если вы думаете - зачем мне это делать с помощью кода VBA. Это легко сделать с помощью встроенного фильтра.

Если это все, что вы хотите сделать, лучше использовать встроенную функцию фильтра.

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

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

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

Пример: несколько критериев (И / ИЛИ) в одном столбце

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

Следующий код сделает это:

Обратите внимание, что здесь я использовал XLOR оператор.

Это указывает VBA использовать оба критерия и фильтровать данные, если выполняется какой-либо из двух критериев.

Точно так же вы также можете использовать критерий И.

Например, если вы хотите отфильтровать все записи, в которых количество больше 10, но меньше 20, вы можете использовать следующий код:

Пример: несколько критериев с разными столбцами

Предположим, у вас есть следующий набор данных.

С помощью автофильтра вы можете фильтровать несколько столбцов одновременно.

Например, если вы хотите отфильтровать все записи, где элемент - «Принтер», а торговый представитель - «Отметка», вы можете использовать следующий код:

Пример: отфильтровать 10 лучших записей с помощью метода автофильтрации

Предположим, у вас есть следующий набор данных.

Ниже приведен код, который предоставит вам 10 лучших записей (на основе столбца количества):

В приведенном выше коде я использовал ActiveSheet. Вы можете использовать имя листа, если хотите.

Обратите внимание, что в этом примере, если вы хотите получить 5 лучших элементов, просто измените число в Criteria1: = ”10 ″ с 10 до 5.

Итак, для топ-5 элементов код будет следующим:

Это может выглядеть странно, но независимо от того, сколько топовых элементов вы хотите, значение Operator всегда остается xlTop10Items.

Точно так же приведенный ниже код даст вам 10 нижних элементов:

А если вам нужны 5 нижних элементов, измените число в Criteria1: = ”10 ″ с 10 до 5.

Пример: отфильтруйте 10 процентов лучших с помощью метода автофильтрации

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

Ниже приведен код, который предоставит вам первые 10 процентов записей (в зависимости от столбца количества):

В нашем наборе данных, поскольку у нас есть 20 записей, он вернет 2 верхние записи (что составляет 10% от общего числа записей).

Пример: использование подстановочных знаков в автофильтре

Предположим, у вас есть набор данных, как показано ниже:

Если вы хотите отфильтровать все строки, в названии которых есть слово «Доска», вы можете использовать следующий код:

В приведенном выше коде я использовал подстановочный знак * (звездочку) до и после слова «Доска» (которое является критерием).

Звездочка может представлять любое количество символов. Таким образом, это отфильтрует любой элемент, в котором есть слово «доска».

Пример: копирование отфильтрованных строк на новый лист

Если вы хотите не только фильтровать записи на основе критериев, но и копировать отфильтрованные строки, вы можете использовать следующий макрос.

Он копирует отфильтрованные строки, добавляет новый лист, а затем вставляет эти скопированные строки в новый лист.

Приведенный выше код проверяет, есть ли отфильтрованные строки в Sheet1 или нет.

И если есть отфильтрованные строки, он скопирует их, вставит новый рабочий лист и вставит эти строки на этот только что вставленный рабочий лист.

Пример: фильтрация данных на основе значения ячейки

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

Что-то вроде того, что показано ниже:

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

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

Ниже приведен код, который это сделает:

Это код события рабочего листа, который выполняется только тогда, когда на листе есть изменение, а целевая ячейка - B2 (где у нас есть раскрывающийся список).

Кроме того, условие If Then Else используется для проверки, выбрал ли пользователь «Все» в раскрывающемся списке. Если выбрано «Все», отображается весь набор данных.

Этот код НЕ помещается в модуль.

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

Вот шаги, чтобы поместить этот код в окно кода рабочего листа:
1. Откройте редактор VB (сочетание клавиш - ALT + F11).
2. На панели Project Explorer дважды щелкните имя рабочего листа, в котором вы хотите использовать эту функцию фильтрации.
3. В окне кода рабочего листа скопируйте и вставьте приведенный выше код.
4. Закройте редактор VB.
Теперь, когда вы используете раскрывающийся список, он автоматически фильтрует данные.

Это код события рабочего листа, который выполняется только тогда, когда на листе есть изменение, а целевая ячейка - B2 (где у нас есть раскрывающийся список).

Кроме того, условие If Then Else используется для проверки, выбрал ли пользователь «Все» в раскрывающемся списке. Если выбрано «Все», отображается весь набор данных.

Включение и выключение автофильтра Excel с помощью VBA

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

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

Этот код проверяет все листы и удаляет все примененные фильтры.

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

Приведенный выше код проверяет, установлены ли уже фильтры или нет.

Если фильтры уже применены, он их удаляет, иначе ничего не делает.

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

Убедитесь, что автофильтр уже применен

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

Показать все данные

Если у вас есть фильтры, примененные к набору данных, и вы хотите показать все данные, используйте приведенный ниже код:

Приведенный выше код проверяет, имеет ли FilterMode значение TRUE или FALSE.

Если это правда, это означает, что был применен фильтр и он использует метод ShowAllData для отображения всех данных.

Обратите внимание, что это не удаляет фильтры. Значки фильтров по-прежнему доступны для использования.

Использование автофильтра на защищенных листах

По умолчанию, когда вы защищаете лист, фильтры не работают.

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

Для этого отметьте опцию Использовать автофильтр при защите листа.

Хотя это работает, когда у вас уже есть фильтры, если вы попытаетесь добавить автофильтры с помощью кода VBA, это не сработает.

Поскольку лист защищен, он не позволит запускать какие-либо макросы и вносить изменения в автофильтр.

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

Это может быть полезно, когда вы создали динамический фильтр (то, что я рассмотрел в примере - «Фильтровать данные на основе значения ячейки»).

Ниже приведен код, который защитит лист, но в то же время позволит вам использовать в нем фильтры, а также макросы VBA.

Этот код необходимо поместить в окно кода ThisWorkbook.

Вот шаги, чтобы поместить код в окно кода ThisWorkbook:

Как только вы откроете книгу и включите макросы, он автоматически запустит макрос и защитит Sheet1.

Однако перед этим он укажет EnableAutoFilter = True, что означает, что фильтры будут работать и на защищенном листе.

Кроме того, он устанавливает для аргумента UserInterfaceOnly значение True. Это означает, что пока рабочий лист защищен, код макроса VBA будет продолжать работать.

Вам также могут понравиться следующие руководства по VBA:

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

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

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

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

Именованный диапазон для условий

Сначала надо создать именованный диапазон, куда мы будем вводить условия, и откуда макрос их будет брать. Для этого можно прямо над таблицей вставить пару-тройку пустых строк, затем выделить ячейки для будущих критериев (на рисунке это A2:F2) и дать им имя Условия, вписав его в поле имени в левом верхнем углу и нажав клавишу Enter. Для наглядности, я выделил эти ячейки желтым цветом:

Макро-рекордер — твой друг (или враг)

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

Вот шаги для создания макроса фильтра с помощью устройства записи макросов:
1. Включите рекордер макросов:
  1. Вкладка «Разработчик»> «Запись макроса».
  2. Дайте макросу имя, выберите, где вы хотите сохранить код, и нажмите ОК.
  Если вы уже использовали макрос-рекордер для этого процесса, то вы знаете, насколько он может быть полезен. Тем более, что наши критерии фильтрации становятся более сложными.
  
  Код будет выглядеть примерно так:
  
  Мы видим, что каждая строка использует метод AutoFilter для применения фильтра к столбцу. Он также содержит информацию о критериях фильтра.
  
  Мы видим, что каждая строка использует метод AutoFilter для применения фильтра к столбцу. Он также содержит информацию о критериях фильтра.
  
  Как сделать фильтр в сводной таблице макросом
  
  Чтобы получить данные для одного из 4-х магазинов фирмы, достаточно лишь открыть выпадающий список со всеми магазинами и выбрать один из них. Пример результата этих действий изображено на рисунке:
  
  Изменение настроек, которые доступны в интерактивных инструментах сводной таблице доступны так же на уровне программирования макросов из редактора VBA. Выбор магазина, который является элементом поля СТРАИЦЫ реализуется с помощью свойства CurrentPage.
  
  Просто как параметр для этого свойства следует указать название поля. Например, напишем простой код макроса, который сам выберет «Магазин3» как критерий для фильтрования данных по оборотам в сводной таблице:
  
  Sub Magazin3()
  ActiveSheet.PivotTables( "ТаблицаМ" ).PivotFields( "Магазины" ).CurrentPage = "Магазин 3"
  End Sub
  
  Чтобы создать такой макрос сначала откройте редактор VisualBasic (ALT+F11), а потом создайте новый модуль в редакторе: «Insert»-«Module» и введете в него выше указанный VBA-код:
  
  Результат действия этого простого макроса такой же как выше рисунке, но без использования пользователем интерактивных инструментов сводной таблицы.
  
  В методе PivotTables указан аргумент «ТаблицаМ» – это всего лишь ссылка на внутренне имя, которое было присвоено для сводной таблицы еще на этапе ее создания. Читайте пример создания где он детально описан: Макрос для создания сводной таблицы в Excel . Далее рассмотрим, как можно скрывать часть данных из области видимости значений.
  
  Решение № 1: Макрос VBA для фильтрации сводной таблицы по определенной дате или периоду
  
  Мы можем использовать простой макрос, чтобы установить фильтр в сводной таблице для самой последней даты в таблице исходных данных. Фильтрация поля «Дата отчета» в сводной таблице также выберет отфильтрованный элемент в срезе и отфильтрует все связанные сводные диаграммы.
  
  Приведенный ниже макрос может выглядеть как много кода, но на самом деле он очень прост. Чтобы его использовать, вам просто нужно будет указать все переменные для имени рабочего листа, имени сводной таблицы, имени сводного поля (Дата отчета) и критериев фильтрации (последняя дата). Эти переменные будут представлять имена объектов в вашей собственной книге.
  
  Макрос в настоящее время настроен на использование значения из ячейки G2 в Таблице данных для критериев фильтра (самая поздняя дата). Ячейка G2 содержит формулу, которая возвращает самую последнюю дату из столбца с помощью функции MAX
  
  Конечно, это можно изменить, чтобы вычислить самую последнюю дату в коде макроса. Хотя приятно видеть это на листе.
  
  Как работает макрос?
  
  Макрос сначала очищает все фильтры для поля сводки фильтра отчетов с помощью метода ClearAllFilters.
  
  Затем он использует цикл For Next для циклического прохождения всех элементов сводки в поле сводки, чтобы применить фильтр. Каждый уникальный элемент в поле является основным элементом. Макрос проверяет, не соответствует ли имя элемента сводки (<>) критериям. Если нет, то он скрывает элемент или фильтрует его. В результате мы видим только критерии фильтра.
  
  Как насчет фильтрации двух сводных таблиц за разные периоды времени?
  
  Вот еще один пример с настройкой переменных в качестве параметров макроса. Это позволяет нам вызывать макрос из другого макроса.
  
  В этом примере у нас есть лист с двумя сводными таблицами для сравнения неделя за неделей. Таким образом, нам нужно отфильтровать одну сводную таблицу для самой последней даты и одну для даты предыдущей недели.
  
  Макрос Filter_Multiple_Pivots вызывает макрос Filter_PivotField_Args дважды. Обратите внимание, что имя сводной таблицы и значения критериев фильтрации различны для каждого вызова. Это простой способ многократно использовать макрос сводного поля фильтра без необходимости повторения большого количества кода.
  
  Настройка: данные конвейера продаж CRM
  
  Для обновления данных мы экспортируем конвейерный отчет каждую неделю и вставляем данные в конец существующей таблицы. Есть и другие способы автоматизации этого процесса, но мы не будем вдаваться в подробности.
  
  В наборе данных есть столбец «Дата отчета», в котором содержится дата запуска отчета для каждой строки. Вы можете видеть на изображении, что есть 4 набора данных, добавленных (сложенных) вместе, чтобы создать одну большую таблицу.
  
  Сводная таблица показывает сводку доходов по этапам конвейера, а поле «Дата отчета» находится в области «Фильтры». Это позволяет нам фильтровать по любой дате отчета, чтобы увидеть сводную информацию о конвейере за эту неделю.
  
  Когда новые данные добавляются в таблицу данных, мы хотим автоматически фильтровать все связанные сводные таблицы, диаграммы и срезы на последнюю дату отчета.
  
  Как установить номер поля динамически
  
  Если мы добавим / удалим / переместим столбцы в диапазоне фильтра, то номер поля для отфильтрованного столбца может измениться. Поэтому я стараюсь по возможности избегать жесткого кодирования числа для параметра Field.
  
  Вместо этого мы можем использовать переменную и использовать некоторый код, чтобы найти номер столбца по его имени. Вот два примера для обычных диапазонов и таблиц.
  
  Номер столбца будет найден при каждом запуске макроса. Нам не нужно беспокоиться об изменении номера поля при перемещении столбца. Это экономит время и предотвращает ошибки (беспроигрышный вариант)!
  
  Добавляем макрос фильтрации
  
  Теперь надо добавить к текущему листу макрос фильтрации по критериям из созданного диапазона Условия. Для этого щелкните правой кнопкой мыши по ярлычку листа и выберите команду Исходный текст (Source text) . В открывшееся окно редактора Visual Basic надо скопировать и вставить текст вот такого макроса:
  
  Private Sub Worksheet_Change(ByVal Target As Range) Dim FilterCol As Integer Dim FilterRange As Range Dim CondtitionString As Variant Dim Condition1 As String, Condition2 As String If Intersect(Target, Range("Условия")) Is Nothing Then Exit Sub On Error Resume Next Application.ScreenUpdating = False 'определяем диапазон данных списка Set FilterRange = Target.Parent.AutoFilter.Range 'считываем условия из всех измененных ячеек диапазона условий For Each cell In Target.Cells FilterCol = cell.Column - FilterRange.Columns(1).Column + 1 If IsEmpty(cell) Then Target.Parent.Range(FilterRange.Address).AutoFilter Field:=FilterCol Else If InStr(1, UCase(cell.Value), " ИЛИ ") > 0 Then LogicOperator = xlOr ConditionArray = Split(UCase(cell.Value), " ИЛИ ") Else If InStr(1, UCase(cell.Value), " И ") > 0 Then LogicOperator = xlAnd ConditionArray = Split(UCase(cell.Value), " И ") Else ConditionArray = Array(cell.Text) End If End If 'формируем первое условие If Left(ConditionArray(0), 1) = "<" Or Left(ConditionArray(0), 1) = ">" Then Condition1 = ConditionArray(0) Else Condition1 = "=" & ConditionArray(0) End If 'формируем второе условие - если оно есть If UBound(ConditionArray) = 1 Then If Left(ConditionArray(1), 1) = "<" Or Left(ConditionArray(1), 1) = ">" Then Condition2 = ConditionArray(1) Else Condition2 = " 0" src="/images/upload/medialibrary/d26/d26aad041f645f5989744873c68403c1.jpg" width="686" height="213">
  
  Как и в случае с классическими Автофильтром (Filter) и Расширенным фильтром (Advanced Filter) , в нашем фильтре макросом можно смело использовать символы подстановки:
  
  и операторы логической связки:
  
  и любые математические символы неравенства (>,<,=,>=,<=,<>).
  
  При удалении содержимого ячеек желтого диапазона Условия автоматически снимается фильтрация с соответствующих столбцов.
  
  Метод автофильтрации
  
  Метод AutoFilter используется для очистки и применения фильтров к одному столбцу в диапазоне или таблице в VBA. Он автоматизирует процесс применения фильтров через выпадающие меню фильтров и делает, чтобы все работало.
  
  Его можно использовать для применения фильтров к нескольким столбцам путем написания нескольких строк кода, по одной для каждого столбца. Мы также можем использовать Автофильтр, чтобы применить несколько критериев фильтрации к одному столбцу, так же, как в выпадающем меню фильтра, установив несколько флажков или указав диапазон дат.
  
  Читайте также: