Excel vba autofilter count

VBA Code to Apply and Control AutoFilter in Excel

AutoFilters are a great feature in Excel. Often they are a quicker way of sorting and filtering data than looping through each cell in a range.

This post provides the main lines of code to apply and control the AutoFilter settings with VBA.

Adapting the code to your needs

Every code snippet below is applied to the ActiveSheet (i.e., whichever sheet which is currently in use at the time the macro runs). It is easy to apply the code to other sheets by changing the section of code which refers to the ActiveSheet.

AutoFilter Icons

When using AutoFilters, the icons at the top of the columns indicate whether any settings have been applied.

Check Auto Filter existence

Each worksheet can only contain one AutoFilter. The following code checks for the existence of an AutoFilter by checking the AutoFilterMode property of the sheet.

Add / Remove an Auto Filter

The AutoFilter will be applied to the “current region” of the cells. The Current Region represents the cells surrounding the selected cell which are not separated by a blank row or column.

Trying to add an AutoFilter to an empty cell will trigger an error message.

Hide / Display Auto Filter drop-down button

The drop-down buttons can be hidden, giving the appearance that there is no AutoFilter on the worksheet. This is great if using VBA to control the AutoFilter as part of a process; the user will not be able to apply their own settings.

Count visible records

After applying a filter, counting the visible cells in the first column will show the number of records meeting the criteria applied.

Get Auto Filter range

The code below will show you the range of cells which are covered by the AutoFilter.

Show everything

Showing everything in the AutoFilter will cause an error if a filter has not been applied.

Apply text filter to a column

The example below shows how to apply a text filter for any value of “A” or “B”.

Advanced filtering is one of the most useful features of AutoFilter. The examples show how to apply different criteria by using Wildcards.

Operators allow multiple filters to be applied to a single column. Filters can be ‘and’ where both criteria are met, or ‘or’ where either criteria is met.

Apply color filter to a column

AutoFilters allow cells to be filtered by color. The RGB color code below can be used to sort by any color.

Clear an existing sort

Apply an alphabetical sort

To apply descending sort, change the Order property to xlDescending:

Apply a custom sort order

Alphabetical and reverse alphabetical may be the most likely sort order, however any custom sort order can be applied. The example below will sort in the order of “L”, “J”, “B” then “Q”.

3 thoughts on “ VBA Code to Apply and Control AutoFilter in Excel ”

what macro code can i use if i want to filter using a cell reference?

what macro code can i use if i want to filter everything except what is indicated in a cell?

note : for these 2 samples, the values in the cells may change from time to time

To filter on a cell reference it would be:

To keep everything except the cell, it would be:

Similar to the above question, I want to filter 5 sheets by 1 criteria (number) that I enter into a cell on a 6th sheet named “Criteria”. I’ve got snippets of VBA for applying autofilter across worksheets, and filter active sheets, but I’m not sure how to combine into what I need.

Leave a Reply Cancel reply

Claim your free download today

Subscribe to the newsletter to receive exclusive content, tips and tricks, tools and downloads.

You will receive the Advanced VLOOKUP Cheat Sheet for FREE.

Excel vba autofilter count

На этом шаге мы рассмотрим назначение и примеры использования этого метода.

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

• Все (All),
• Первые десять (Тор 10),
• Условие (Custom),
• конкретный элемент данных,
• Пустые (Blanks) и
• Непустые (NohBlanks).

Вручную метод запускается посредством выбора команды Данные | Фильтр | Автофильтр (Data | Filter | AutoFilter).

При применении метода AutoFilter допустимы два синтаксиса.

При работе с фильтрами полезны метод ShowAllData и свойства FilterMode и AutoFilterMode.

• Метод ShowAllData — показывает все отфильтрованные и неотфильтрованные строки рабочего листа.
• Свойство FilterMode — допустимые значения: True (если на рабочем листе имеются отфильтрованные данные со скрытыми строками), False (в противном случае).
• Свойство AutoFilterMode — допустимые значения: True (если на рабочем листе выведены раскрывающиеся списки метода AutoFilter), False (в противном случае).

Приведем соответствие между аргументами метода AutoFilter и выполнением команды Данные | Фильтр | Автофильтр(Data | Filter | AutoFilter) при фильтрации базы данных регистрации туристов.

Выделяем диапазон A1:E1, содержащий заголовки полей базы данных. Выберем команду Данные | Фильтр | Автофильтр(Data | Filter | AutoFilter). В результате в заголовках полей появятся раскрывающиеся списки (рисунок 1).

Рис.1. Раскрывающиеся списки метода AutoFilter

В этих раскрывающихся списках предлагаются варианты допустимой фильтрации. В методе AutoFilter за диапазон с названиями полей отвечает объект, к которому применяется метод. В данном случае метод AutoFilter надо применить к диапазону Range(«A1:E1»).

Отфильтруем в базе данных, например, только данные о клиентах, направляющихся в Афины (рисунок 2).

Рис.2. Фильтрация списка по критерию Афины

С этой целью в раскрывающемся списке поля Направление тура выберем Афины. В результате на рабочем листе будут выведены только записи, соответствующие турам в Афины. В методе AutoFilter за выбор поля, в котором производится фильтрация, отвечает аргумент Field. В данном случае для выбора поля Направление тура аргументу Field надо присвоить значение 4. За критерии, покоторым производится фильтрация, отвечают аргументы criteria1 и criteria2. В данном случае фильтрация производилась по одному критерию — Афины, поэтому только аргументу criteria1 надо присвоить значение Афины. Таким образом, имеем:

Рис.3. Диалоговое окно Пользовательский автофильтр

Например, отфильтруем все туры в Афины и Берлин. В методе AutoFilter это соответствует присвоению аргументам criteria1 и criteria2 значений Афины и Берлин соответственно, а аргументу operator — значения хlOr, т.к. будут отображаться либо туры в Афины, либо в Берлин. Таким образом, имеем:

На следующем шаге мы рассмотрим метод AdvancedFilter.

Что делать, когда autofilter в VBA не возвращает данные?

2 lakesh [2016-04-12 05:23:00]

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

Это дает мне ошибку при

Когда данных вообще нет, возвращается ошибка: «Ячейки не найдены».

Но это не помогло. Необходимо руководствоваться тем, как решить эту проблему.

vba excel autofilter

4 ответа

2 Решение brettdj [2016-04-12 06:38:00]

Попробуйте выполнить обработку ошибок следующим образом:

Подход без обработки ошибок

Можно создать AutoFilter таким образом, чтобы он не выдавал ошибку, если ничего не найдено. Трюк состоит в том, чтобы включить строку заголовка в вызов SpecialCells . Это обеспечит видимость хотя бы 1 строки, даже если ничего не найдено (Excel не скроет строку заголовка). Это предотвращает застревание ошибки и дает набор ячеек для проверки наличия данных.

Чтобы проверить, имеет ли результирующий диапазон данные, вам необходимо проверить Rows.Count > 1 Or Areas.Count > 1 . Это обрабатывает два возможных случая, когда ваши данные находятся непосредственно под заголовком или в прерывистом диапазоне ниже строки заголовка. Любой результат означает, что AutoFilter нашел допустимые строки.

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

Пример данных [столбец C (поле 2) будет отфильтрован]:

Результат с критериями 1: = 64

Immediate window: found data

Результат с критериями 1: = 0

Immediate window: only header, no data included

Другие примечания:

• Код содержит отдельную переменную rngData если вы хотите получить доступ к данным без заголовков. Это просто INTERSECT-OFFSET, чтобы он ударил одну строку вниз.
• Для случая, когда результат был найден, код сбрасывает rngVisible как видимые ячейки только в данных (пропускает заголовок). Поскольку этот вызов не может завершиться неудачно, он безопасен без обработки ошибок. Это дает вам диапазон, который соответствует тому, что вы пробовали в первый раз, но без возможности получить erorr. Это не требуется, если вы можете обработать исходный диапазон rngVisible который включает заголовки. Если это так, вы можете полностью rngData (если у вас нет другой необходимости).

0 Jason [2016-04-12 05:37:00]

Вы можете ввести код в функцию.

В функции используйте ошибку goto xxxx. Когда ошибка не возвращает ничего из функции и использует «если myRange не является чем-то тогда», чтобы игнорировать ячейки ошибок.

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

где я также предложил некоторые настройки переменной рабочей книги и рабочего листа для «облегчения» кодирования жизни

Excel VBA AutoFilter to Filter Data Table

The VBA Tutorials Blog

Use VBA AutoFilter to filter data in Excel. The VBA AutoFilter method lets you filter multiple fields at once and even filter on more than one criteria.

If you have a big table of data, like employee information, you might need to filter it by certain criteria in order to generate reports.

Today we’ll dive into how to use VBA AutoFilter to do that. AutoFilter is a nifty little way to perform database-like filtering in Excel. It simply hides the rows that don’t match your criteria so you can ignore irrelevant information.

Although AutoFiltering can be done manually directly in Excel’s GUI, it’s much funner to do it programmatically with VBA. It’s pretty simple and quite practical. Let’s pretend you need to print multiple reports based on the same dataset. How nice would it be to be able to automatically filter and manipulate your data and print all your reports in rapid succession? AutoFilter with VBA makes that possible!

Our Dataset

We are going to use a small table so you can inspect the results yourself, but if you were working with tens of thousands of entries, you would have to trust your programming skills. That is why it is so important to understand the logic of your code. The longer your code takes to run, the more inconvenient it is to make changes as you go. More importantly, you’re not able to physically inspect tens of thousands of rows of data in any reliable way.

If you’re not confident with your programming abilities, I encourage you to subscribe to my free wellsrPRO VBA Training Program using the form below.

Anyway, this is our dataset of very high-powered employees, though you wouldn’t know it from their salaries and the size of the company.

Table of Employees along with some important information

Using a Single Column as the Criteria

If you know which column you want to filter, you can set the filter on a single column rather than the whole table. Let’s say you want to find people in the marketing department. You could simply filter on column E, because Excel will hide every row that does not fit the criteria.

To use the AutoFilter method, you will need a base range, which you can declare like this:

Now, you can run this code to find everyone in the marketing department:

If you prefer positional arguments, you could also write the filtering line like this:

Either way, the end result is this:

A table filtered on «Department» only

There are two things to take note of here:

1. Excel’s filter is only applied to Column E, so if you need to change things manually, you might need to reapply the filter
2. Several rows are hidden, even though we only filtered on Column E

Clear AutoFilter with VBA

If you try to run a second AutoFilter after applying one, like in the example above, VBA will try to stack your filters. You’ll be applying multiple filters to the same dataset. That might be exactly what you you want to do, and we’ll show you a good example of this later, but chances are it’s not what you intended.

If you don’t want to apply multiple filters to the same dataset, you need to clear your filters before applying your new filter. There are a number of ways to do this, but I’ll show you two ways.

Method 1: VBA ShowAllData

This approach keeps all the filters intact, but it uses the ShowAllData method to show all the data in all your filtered ranged. In other words, it basically turns all your filters into empty filters. A filter is still applied, but it’s just not filtering anything.

Method 2: VBA AutoFilterMode

This method completely disables all the filters on your active sheet. It starts you off with a clean slate!

If you don’t want to apply multiple filters to your data, it’s a good idea to run one these two macros before calling the VBA AutoFilter method. The same advice applies if you’re stepping through this tutorial sequentially. Not clearing your filters may even generate an error.

Filtering on a Full Table

Now that we’ve shown you how to clear your filters, let’s say you want to filter an entire table using the VBA AutoFilter approach. There are (at least) two ways to declare the range for your entire table.

If your tables are thousands of rows long or you just want Excel to filter the entire column, you can use columns as the range:

Otherwise, you could set the top left and bottom right corner cells as the range, like this:

A good reason to do this more limited filtering implementation might be the following scenario, where you have two tables — one on top of the other. This might be a rather poor design scheme, but bear with me.

Two tables in the same columns with a blank row for a separator

Now, using our range_to_filter = Range(«A1:E9») example, we can filter just the first table and leave the second one untouched. If we used Range(«A:E») , we would inadvertently filter the second table, even though it is irrelevant to our search of the first table!

The Arguments

There are five arguments for VBA AutoFilter, all of which are optional. If you don’t enter any arguments, you’ll just be toggling empty filters on and off

• Field — the index of the column you want to filter on, with the leftmost column indexed at one (not zero).
• Criteria1 — the first criterion to filter on, always a string, such as “Marketing” or “>30000” (the exception is when the criteria is a single number, like 20000)
• Operator— an Excel operator that makes fulfilling certain filter requirements easier (like top 10% of the table)
• Criteria2 — used in conjunction with Criteria1 for compound filtering (perhaps you need something between 35000 and 45000)
• VisibleDropDown — TRUE or FALSE argument that either shows or hides the dropdown arrow for the filtered fields

Example with Field and Criteria1

We already saw this above, but there was only one field (Column E), so let’s try when there are multiple fields.

Let’s say you want to filter the table on Base Pay with Range = («A1:E9») . In that case, “Base Pay” is the fourth field. Our criterion will be base pay greater than $45,000.

The entire code block would look like this:

And the final result is:

Employee table is filtered by base pay, the email/security table is untouched

Apparently our marketing team gets paid well while everyone else does not.

Adding A Second Criterion

Perhaps you want to view a range of salaries, with an upper and lower limit. The AutoFilter Criteria2 argument is an excellent way to accomplish this:

The Employee table filtered on base pay between $40,000 and $70,000

The Operator Argument

You can specify several “operators” with you VBA AutoFilter code. Operators do neat things, like filtering the top 10% of your data. Going back to our dataset, let’s say you want to see the highest paid 10% of your employees. You would use the xlTop10Percent Operator argument when applying your VBA AutoFilter.

And you end up with just Mr. Mozart in your list. Because we only have 8 employees, the Top 10 percent will be 0.8 of a person, which rounds up to one.

You can modify the Operator argument using Criteria1 , too. This works for the Top and Bottom 10 percent or items options.

For example, you might want the three lowest paid people (so you can give them a raise, of course). To find them, you could use this snippet:

That will leave us with Mr. Smith, Mr. Sejong, and Mr. Nakamoto. All of whom should get raises.

Connecting Criteria1 and Criteria2

Technically, the Operator argument connects the Criteria1 and Criteria2 arguments. The default is xlAnd , the logical AND, so if you have both Criteria1 and Criteria2 , the operator will require both to be true to display the row in question.

A creative use of the operator might be to find outliers in your dataset. Perhaps you want to find very high paid or low paid people. In that case, you can run this code:

leaves us with just Mr. Smith and Mr. Mozart, our lowest and highest paid employees, respectively. As you can see, I didn’t follow the positions set out in AutoFilter , so I need to name each argument and follow it by :=.

Like I said earlier, AutoFilter operators are pretty neat. Take a look at this table and play around with them!

отфильтровать несколько критериев с помощью excel vba

у меня есть 8 переменных в столбце A, 1,2,3,4,5 и A, B, C.

моя цель-отфильтровать A, B, C и отобразить только 1-5.

Я могу сделать это, используя следующий код:

но что делает код, это фильтрует переменные от 1 до 5 и отображает их.

Я буду делать противоположное, но давать тот же результат, отфильтровывая A, B, C и показывая переменные от 1 до 5

Я пробовал этот код:

но это не работать.

почему я не могу использовать этот код ?

это дает эту ошибку:

Ошибка времени выполнения 1004 метод автофильтра класса диапазона не удалось

как я могу выполнить это?

5 ответов

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

интересно, если у вас есть буквальные значения «<>A» и «<>B» в списке и фильтре на этих макрос рекордер придумывает

, которая работает. Но если вы имеют буквальное значение «<>C» а также, и вы фильтруете для всех трех (используя галочки) во время записи макроса, макрос рекордер реплицирует именно ваш код, который затем терпит неудачу с ошибкой. Думаю, я бы назвал это ошибкой — есть фильтры, которые вы можете сделать, используя пользовательский интерфейс, который вы не можете сделать с VBA.

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

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

1. используйте «вспомогательный столбец» с формулой в столбце B, а затем отфильтруйте это — например =ISNUMBER(A2) или фильтр TRUE
2. если вы не можете добавить столбец, используйте автофильтр с Criteria1:=»>-65535″ (или подходящее число ниже, чем вы ожидаете), которое будет отфильтровывать нечисловые значения-предполагая, что это то, что вы хотите
3. напишите суб VBA, чтобы скрыть строки (не совсем то же самое, что автофильтр, но это может быть достаточно, в зависимости от ваших потребностей).

Я не нашел никакого решения в интернете, поэтому я реализовал его.

код Автофильтра с критериями тогда

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

код VBA этого метода

этот код, безусловно, может быть улучшен при возврате массива строк, но работе с Массив в VBA непростой.

Внимание: этот код работает, только если вы определяете лист с именем X, потому что параметр CopyToRange, используемый в AdvancedFilter () нужен диапазон Excel !

жаль, что Microfsoft не реализовал это решение, добавив просто новое перечисление как xlNotFilterValues ! . или xlRegexMatch !

альтернатива с помощью функции фильтра VBA

в качестве инновационной альтернативы недавнему ответу @schlebe я попытался использовать Filter функция интегрирована в VBA, позволяющего из данную строку поиска третий аргумент в false. Все!—11—>»негативный» строки поиска (например, A, B, C) определяются в массиве. Я читаю критерии в столбце A в массив полей данных и в основном выполняю последующее фильтрация (A-C) для фильтрации этих элементов.