TL;DR: чтобы быстрее всего реализовать и понять метод, прокрутите вниз до второго варианта со столбцом «помощник». Вернитесь, чтобы изучить другие методы. когда у вас есть время, чтобы сопоставить новые методы с вашей конкретной ситуацией
и максимизировать эффективность.
Как упоминалось в комментариях, оператор xlOr в Метод Range.AutoFilter не работает с несколькими полями; только для одного поля. Насколько я понимаю, у вас есть как минимум пять вариантов.
Вариант 1. метод расширенного фильтра
Настройте область для критериев расширенного фильтра. Далее я использовал AC1:AD3. Критерии Advanced Filter OR идут в последовательных строках.
Пример данных с Расширенный фильтр область критериев
Как код:
Dim crit As Range
With Worksheets("Sheet1")
If .AutoFilterMode Then .AutoFilterMode = False
.Range("AC1:AD1") = Array(.Range("L1").Value, .Range("AA1").Value)
.Range("AC2") = "ABC"
.Range("AD3") = "<>DEF"
Set crit = .Range("AC1:AD3")
With .Cells(1, 1).CurrentRegion
.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=crit, Unique:=False
End With
End With
Вариант 2. .AutoFilter на "помощнике" столбец с формулой рабочего листа
«Вспомогательный» столбец — это простой способ сделать несколько определений. Поместите простую формулу, которая разрешается в TRUE/FALSE на основе ваших нескольких критериев (например, =OR($L2="ABC",$AA2<>"DEF")
), в неиспользуемый столбец и .AutoFilter в этом столбце.
Пример данных, показывающих формулу рабочего листа в столбце "Вспомогательный"
Как код:
With Worksheets("Sheet1")
If .AutoFilterMode Then .AutoFilterMode = False
With .Cells(1, 1).CurrentRegion
With .Resize(.Rows.Count - 1, 1).Offset(1, .Columns.Count)
.Formula = "=OR($L2=""ABC"",$AA2<>""DEF"")"
End With
End With
With .Cells(1, 1).CurrentRegion
.AutoFilter Field:=.Columns.Count, Criteria1:="TRUE"
'clean up the 'helper' column and refresh the last_cell afterwards
If False Then
.Columns(.Columns.Count).EntireColumn.Delete
.Parent.UsedRange
End If
End With
End With
Держите формулу максимально простой. Не забудьте удалить столбец «помощник» и обновить свойство xlCellTypeLastCell после завершения работы с отфильтрованными результатами.
Вариант 3. .AutoFilter на условный Правило форматирования
Та же логика, что и в решении «вспомогательного» столбца, может быть применена с помощью правило условного форматирования и .AutoFilter может фильтровать цвет, применяемый к совпадающим строкам.
Пример данных, показывающий условное форматирование правило
Как код:
With Worksheets("Sheet1")
If .AutoFilterMode Then .AutoFilterMode = False
With .Cells(1, 1).CurrentRegion
With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=OR($L2=""ABC"",$AA2<>""DEF"")"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).Font.Color = vbRed
End With
.AutoFilter Field:=1, _
Criteria1:=vbRed, Operator:=xlFilterFontColor
With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
.FormatConditions(1).Delete
End With
End With
End With
Очевидным преимуществом здесь является отсутствие столбца формул для «очистки».
Вариант 4. Использование объекта Scripting.Dictionary в столбце с уникальными значениями
Если у вас есть столбец, содержащий уникальные значения, выполните цикл по строкам, сравнивая ваши критерии со значениями в столбцах L и AA. Если они совпадают, запишите значение из уникального столбца в Scripting.Dictionary. ключи и использовать ключи как массив критериев.
Пример данных, показывающий правило условного форматирования
Как код:
Dim d As Long, dict As Object
Set dict = CreateObject("Scripting.Dictionary")
With Worksheets("Sheet1")
If .AutoFilterMode Then .AutoFilterMode = False
With .Cells(1, 1).CurrentRegion
For d = 2 To .Rows.Count
If LCase(.Cells(d, "L")) = "abc" Or LCase(.Cells(d, "AA")) <> "def" Then _
dict.Add Key:=CStr(.Cells(d, "A").Text), Item:=vbNullString
Next d
With .Columns(1)
.AutoFilter Field:=1, Criteria1:=dict.keys, Operator:=xlFilterValues
End With
End With
End With
dict.RemoveAll: Set dict = Nothing
Это может показаться слишком трудоемким по сравнению с другими методами, но в конечном итоге это быстрее для больших блоков данных со сложными критериями¹.
Вариант 5: псевдоавтофильтр с Range.Hidden в свойстве Range.EntireRow
Это может быть самым простым решением. Просто просмотрите строки, сравнивая критерии со столбцами L и AA. Соберите строки, которые не соответствуют Union и примените Range.Hidden в наборе свойств Range.EntireRow.
Примеры строк данных для получения ссылки Range.Hidden настройка свойства
Как код:
Dim d As Long, rng As Range
With Worksheets("Sheet1")
If .AutoFilterMode Then .AutoFilterMode = False
With .Cells(1, 1).CurrentRegion
.EntireRow.Hidden = False
For d = 2 To .Rows.Count
If (LCase(.Cells(d, "L").Value2) <> "abc" And _
LCase(.Cells(d, "AA").Value2) = "def") Then
If rng Is Nothing Then
Set rng = .Rows(d)
Else
Set rng = Union(rng, .Rows(d))
End If
End If
Next d
rng.EntireRow.Hidden = True
End With
End With
Помните, что вам нужно обратить критерии, поскольку вы планируете скрыть строки, которые не соответствуют. Предупреждение здесь заключается в том, что вам нужно выбрать свойство Range.CurrentRegion и отобразите строки, чтобы удалить фильтр имитации.
Результаты:
Любой из пяти описанных выше методов должен давать результаты, аналогичные приведенным ниже.
Результаты после применения любого из методов фильтрации
¹ См. Может ли автофильтр принимать как инклюзивные, так и неинклюзивные подстановочные знаки из ключей словаря? примеры сложных множественных критериев с использованием Scripting.Dictionary в качестве массива критериев для метода Range.AutoFilter
person
Community
schedule
08.04.2016
Field:=27
? - person   schedule 08.04.2016