Advanced filter vba не работает

200?’200px’:»+(this.scrollHeight+5)+’px’);»> 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) = » » Then
Condition1 = ConditionArray(0)
Else
Condition1 = «=» & ConditionArray(0)
End If
‘формируем второе условие — если оно есть
If UBound(ConditionArray) = 1 Then
If Left(ConditionArray(1), 1) = » » Then
Condition2 = ConditionArray(1)
Else
Condition2 = «=» & ConditionArray(1)
End If
End If
‘включаем фильтрацию
If UBound(ConditionArray) = 0 Then
Target.Parent.Range(FilterRange.Address).AutoFilter Field:=FilterCol, Criteria1:=Condition1
Else
Target.Parent.Range(FilterRange.Address).AutoFilter Field:=FilterCol, Criteria1:=Condition1, _
Operator:=LogicOperator, Criteria2:=Condition2
End If
End If
Next cell

Set FilterRange = Nothing
Application.ScreenUpdating = True
End Sub

200?’200px’:»+(this.scrollHeight+5)+’px’);»> 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

Set FilterRange = Nothing
Application.ScreenUpdating = True
End Sub

200?’200px’:»+(this.scrollHeight+5)+’px’);»> 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

Источник

Advanced.Filter VBA

I have this code so far, only does it for sheet 2, how can I alter this code to include multiple sheets into this? Complete newb here. :

2 Answers 2

You can do it like that:

Filter data in place:

Filter data and paste them into a new worksheet:

Unique data from each worksheet are printed in the first column of a new worksheet called Unique data.

This method filters data from each worksheet separately, so if there is for example value A in Sheet1 and value A in Sheet2, there will be two entries A in the result list.

Note that first value is considered to be a header and it can be duplicated in the result list.

I feel your comments warrant me posting this as an answer so that I may be a bit more thorough. This is meant only to add to the answer provided by mielk!

The object hierarchy in excel is roughly summarized by «An Excel Application owns workbooks. An Excel Workbook owns Worksheets. An Excel Worksheet owns Ranges.» For more info on that look here.

When you click on an excel file to open it you are effectively doing 2 things:

Starting up an Excel «Application»
Opening up a Workbook that that «Application» will «own»

When you open up subsequent Excel files, Excel will skip step one and simply open a workbook in the Excel Application that is already running. Note this means that similar to how a Workbook can have many Worksheets a single Excel Application can have multiple Workbooks that belong to it.

There are multiple ways to access these workbooks in VBA. One way is to use the application’s Workbooks member much like you used a Workbook ‘s Sheets member to access worksheets. Often though you simply want to access the Workbook that the user is currently editing/working on. To do this you can use ActiveWorkbook which is automatically updated for you whenever the user begins work on a different workbook.

Another Workbook you will often want to use is the workbook that «houses» the code you are running. You can do this by using ThisWorkbook . If you open up the VBA editor and look at the project viewer, you can even see a reference to ThisWorkbook ! If you want your code to only update/alter the workbook that contains it then ThisWorkbook is the way to go.

Let’s say you have a macro to loop through all of the open workbooks and put the number of sheets each Workbook «owns» into some Worksheet in the «master» workbook.

Читайте также: Hydra tor не работает

You could do something like this:

You would put this code as a module in the «Master» workbook.

Let me know if this clears things up for you! 🙂

Источник

Расширенный фильтр и немного магии

У подавляющего большинства пользователей Excel при слове «фильтрация данных» в голове всплывает только обычный классический фильтр с вкладки Данные — Фильтр (Data — Filter) :

Такой фильтр — штука привычная, спору нет, и для большинства случаев вполне сойдет. Однако бывают ситуации, когда нужно проводить отбор по большому количеству сложных условий сразу по нескольким столбцам. Обычный фильтр тут не очень удобен и хочется чего-то помощнее. Таким инструментом может стать расширенный фильтр (advanced filter), особенно с небольшой «доработкой напильником» (по традиции).

Основа

Для начала вставьте над вашей таблицей с данными несколько пустых строк и скопируйте туда шапку таблицы — это будет диапазон с условиями (выделен для наглядности желтым):

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

Именно в желтые ячейки нужно ввести критерии (условия), по которым потом будет произведена фильтрация. Например, если нужно отобрать бананы в московский «Ашан» в III квартале, то условия будут выглядеть так:

Чтобы выполнить фильтрацию выделите любую ячейку диапазона с исходными данными, откройте вкладку Данные и нажмите кнопку Дополнительно (Data — Advanced) . В открывшемся окне должен быть уже автоматически введен диапазон с данными и нам останется только указать диапазон условий, т.е. A1:I2:

Обратите внимание, что диапазон условий нельзя выделять «с запасом», т.е. нельзя выделять лишние пустые желтые строки, т.к. пустая ячейка в диапазоне условий воспринимается Excel как отсутствие критерия, а целая пустая строка — как просьба вывести все данные без разбора.

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

Добавляем макрос

«Ну и где же тут удобство?» — спросите вы и будете правы. Мало того, что нужно руками вводить условия в желтые ячейки, так еще и открывать диалоговое окно, вводить туда диапазоны, жать ОК. Грустно, согласен! Но «все меняется, когда приходят они ©» — макросы!

Работу с расширенным фильтром можно в разы ускорить и упростить с помощью простого макроса, который будет автоматически запускать расширенный фильтр при вводе условий, т.е. изменении любой желтой ячейки. Щелкните правой кнопкой мыши по ярлычку текущего листа и выберите команду Исходный текст (Source Code) . В открывшееся окно скопируйте и вставьте вот такой код:

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

Так все гораздо лучше, правда? 🙂

Реализация сложных запросов

Теперь, когда все фильтруется «на лету», можно немного углубиться в нюансы и разобрать механизмы более сложных запросов в расширенном фильтре. Помимо ввода точных совпадений, в диапазоне условий можно использовать различные символы подстановки (* и ?) и знаки математических неравенств для реализации приблизительного поиска. Регистр символов роли не играет. Для наглядности я свел все возможные варианты в таблицу:

Критерий	Результат
гр* или гр	все ячейки начинающиеся с Гр , т.е. Груша, Грейпфрут, Гранат и т.д.
=лук	все ячейки именно и только со словом Лук, т.е. точное совпадение
лив или *лив	ячейки содержащие лив как подстроку, т.е. Оливки, Ливер, Залив и т.д.
=п*в	слова начинающиеся с П и заканчивающиеся на В т.е. Павлов, Петров и т.д.
а*с	слова начинающиеся с А и содержащие далее С , т.е. Апельсин, Ананас, Асаи и т.д.
=*с	слова оканчивающиеся на С
=.	все ячейки с текстом из 4 символов (букв или цифр, включая пробелы)
=м. н	все ячейки с текстом из 8 символов, начинающиеся на М и заканчивающиеся на Н , т.е. Мандарин, Мангостин и т.д.
=*н??а	все слова оканчивающиеся на А , где 4-я с конца буква Н , т.е. Брусника, Заноза и т.д.
>=э	все слова, начинающиеся с Э , Ю или Я
<>о	все слова, не содержащие букву О
<>*вич	все слова, кроме заканчивающихся на вич (например, фильтр женщин по отчеству)
=	все пустые ячейки
<>	все непустые ячейки
>=5000	все ячейки со значением больше или равно 5000
5 или =5	все ячейки со значением 5
>=3/18/2013	все ячейки с датой позже 18 марта 2013 (включительно)

Знак * подразумевает под собой любое количество любых символов, а ? — один любой символ.
Логика в обработке текстовых и числовых запросов немного разная. Так, например, ячейка условия с числом 5 не означает поиск всех чисел, начинающихся с пяти, но ячейка условия с буквой Б равносильна Б*, т.е. будет искать любой текст, начинающийся с буквы Б.
Если текстовый запрос не начинается со знака =, то в конце можно мысленно ставить *.
Даты надо вводить в штатовском формате месяц-день-год и через дробь (даже если у вас русский Excel и региональные настройки).

Логические связки И-ИЛИ

Условия записанные в разных ячейках, но в одной строке — считаются связанными между собой логическим оператором И (AND) :

Т.е. фильтруй мне бананы именно в третьем квартале, именно по Москве и при этом из «Ашана».

Если нужно связать условия логическим оператором ИЛИ (OR) , то их надо просто вводить в разные строки. Например, если нам нужно найти все заказы менеджера Волиной по московским персикам и все заказы по луку в третьем квартале по Самаре, то это можно задать в диапазоне условий следующим образом:

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

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

Источник

Advanced filter vba не работает

После использования расширенного фильтра не работает функция subtotal

Advanced filter vba не работает

Advanced.Filter VBA

2 Answers 2

Расширенный фильтр и немного магии

Основа

Добавляем макрос

Реализация сложных запросов

Логические связки И-ИЛИ