Использовать автофильтр для текущих видимых данных
Приведенный ниже код используется для установки Autofiler данных на активном листе.
Это работает, но если я позже использовал автофильтр для любого столбца, все скрытые строки снова отображаются.
Моя цель - использовать вспомогательный столбец и установить фильтр по значению.
Заранее спасибо за любую помощь.
Option Explicit
Option Compare Text
Sub AutoFilter_on_visible_data()
Dim ws As Worksheet, arr, i As Long, lastR As Long, HdRng As Range, rng As Range
Set ws = ThisWorkbook.ActiveSheet
lastR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
arr = ws.Range("A3:R" & lastR).Value2 'Place the relevant columns in an array for faster iteration
For i = 1 To UBound(arr)
If ws.Rows(i + 2).Hidden = False Then '(i + 2) because Data starts at Row_3
If Not arr(i, 2) Like "*Oil*" And _
Not arr(i, 5) Like "*-SYS-14" And _
Not arr(i, 6) Like "*Oil" Then
addToRange HdRng, ws.Range("A" & i + 2) 'Make a union range of the rows NOT matching criteria...
End If
End If
Next i
Application.ScreenUpdating = False
If Not HdRng Is Nothing Then HdRng.EntireRow.Hidden = True 'Hide not matching criteria rows.
Application.ScreenUpdating = True
End Sub
Private Sub addToRange(rngU As Range, rng As Range)
If rngU Is Nothing Then
Set rngU = rng
Else
Set rngU = Union(rngU, rng)
End If
End Sub
Я сейчас вне офиса, строка заголовка находится на втором, я проверю сегодня на своем ноутбуке и сообщу вам.
Без проблем. Протестируйте, когда сможете.
Ответы 2
Пожалуйста, попробуйте использовать следующий адаптированный код. Предполагается, что заголовки существуют во второй строке.
Он допускает обработку диапазонов, содержащих строки, скрытые автофильтром, а не только вручную. ShowAllData не отображает строки, сделанные скрытыми вручную, и должен быть размещен после обработки отфильтрованной строки:
Sub AutoFilter_on_visible_data()
Dim ws As Worksheet, arr, i As Long, lastR As Long, lastCol As Long, arrH, rngH As Range, rng As Range
Const helpH As String = "HelpColumn"
Set ws = ThisWorkbook.ActiveSheet
lastR = ws.Range("A" & ws.rows.count).End(xlUp).row
lastCol = ws.cells(2, ws.Columns.count).End(xlToLeft).column 'last column, supposing that the header exists on the second row
Set rngH = ws.rows(2).Find(what:=helpH, LookIn:=xlValues, Lookat:=xlWhole)
If Not rngH Is Nothing Then
lastCol = rngH.column
Else
lastCol = lastCol + 1
ws.cells(2, lastCol).Value = helpH
End If
arr = ws.Range("A3:R" & lastR).Value2 'Place the relevant columns in an array for faster iteration
ReDim arrH(1 To UBound(arr), 1 To 1)
For i = 1 To UBound(arr)
If ws.rows(i + 2).Hidden = False Then '(i + 2) because Data starts at Row_3
If Not arr(i, 2) Like "*Oil*" And _
Not arr(i, 5) Like "*-SYS-14" And _
Not arr(i, 6) Like "*Oil" Then
arrH(i, 1) = "HH" 'Make a helper array to filter on it.
End If
End If
Next i
If Not ws.AutoFilterMode Then rng.AutoFilter 'autofilter the resized range
ws.AutoFilter.ShowAllData
lastR = ws.Range("A" & ws.rows.count).End(xlUp).row 'recalculate the last row after showing all
Set rng = ws.Range(ws.cells(2, 1), ws.cells(lastR, lastCol)) 'to use it for filterring
'clear the previous HH strings not included in the helper column because of the filter:
ws.Range(ws.cells(3, lastCol), ws.cells(lastR, lastCol)).ClearContents
'Drop the arrH content at once:
ws.cells(3, lastCol).Resize(UBound(arrH), 1).Value2 = arrH
'Filter on the helper column:
rng.AutoFilter field:=lastCol, Criteria1: = "HH", Operator:=xlFilterValues
End Sub
Скрытые строки существуют, потому что для любого столбца уже применен фильтр, я попробовал ваш код, и (1) он выведет неверный результат при последовательных запусках, конечно, я могу очистить lastCol перед запуском макроса, но это не оптимальный метод. (2) Я думал, что вы найдете лучший способ, чем мой, при проверке видимого диапазона, чем If ws.rows(i + 2).Hidden = False Then , в вашем другом ответе Ссылка вы использовали хитрый способ справиться с этой точкой.
@ Валид 1. Я не могу понять, почему. Уточните, пожалуйста, как вы поступите после запуска один раз. Теоретически «arrH» также должен включать скрытые строки, а это означает, что возможное «ЧЧ» в скрытой строке не имеет значения. Когда ReDim1 все пусто (включая скрытые строки). 2. Меня не волновала "проверка на видимый диапазон". Я пошел по вашему пути, так как вы сказали, что код работает. Затем мой «другой ответ» делает что-то другое, и я также использовал метод проверки, который вы там пробовали. В любом случае, оба метода одинаковы с точки зрения скорости и элегантности...
@Waleed Я написал комментарий выше, ничего не тестируя, а только глядя на код. Я сделал два теста сейчас, и похоже, что он ведет себя (в моем случае) так, как должен. Если я все еще что-то упускаю, вы можете использовать Else в выражении If (If Not arr(i, 2) Like "*Oil*" And ...) и поместить туда arrH(i, 1) = "". Но это выглядит для меня бесполезным. Попробуйте следующий способ тестирования: вставьте Debug.Print UBound(arrH):Stop сразу после ReDim. Сколько строк он показывает? Не включены скрытые строки?
@Waleed Разве вы не скрываете строки только вручную?
Пожалуйста, рассмотрите этот сценарий: во-первых, я запускаю ваш код, не устанавливая автофильтр для любого столбца (стрелки фильтра все еще отображаются в excel), затем я устанавливаю фильтр для любого отдельного столбца, например "A", затем снова запускаю макрос, в результате автофильтр очищается из столбца A и установить только на lastCol, предполагаемый результат состоит в том, чтобы сохранить фильтр в столбце A, а также добавить фильтр в lastCol
@Waleed Итак, вы не только скрываете строки вручную ... Вы используете предварительные фильтры. Это немного отличается от того, что вы делаете, как я понял. Я попробую адаптировать его, чтобы выполнить то, что вам нужно для описанного (предварительная фильтрация) сценария.
@Waleed Пожалуйста, проверьте версию, опубликованную после редактирования. Он должен работать и для отфильтрованных строк...
Теперь отредактированный ответ работает отлично, как и предполагалось 👍
Пожалуйста, теперь отредактированный ответ является рабочим, но я заметил, что вы всегда оставляете также свой исходный код (не рабочий), для меня (как для обычного пользователя) я столкнулся с конфликтом (без обид), когда я ищу ответ .
@Waleed Не совсем ... Я обычно оставляю там первую версию, только если она может кому-то помочь. Сейчас, боюсь, никому не будет интересно, поэтому удалю.
@Валид, хорошо. Если еще не решил, посмотрю...
по этому вопросу ссылкаregex ответ не является полностью надежным. Пожалуйста, может ли помочь ваш обширный опыт работы с VBA, особенно с массивами?
@ Валид, я не уверен, что смогу тебя достать ... О какой ненадежности ты говоришь? То, что использование функции как UDF не так быстро или что-то еще? Я сейчас выхожу из своего кабинета...
Я еще не тестировал его на скорость, потому что он извлекает некоторые неправильные числа.
чтобы сэкономить ваше время на этот вопрос Ссылка ответ на регулярное выражение теперь принят, хотя я уверен, что его можно выполнить с помощью массивов
@Waleed Я не вижу, как решить проблему с помощью массивов. Только для создания функции с использованием стандартного VBA (InStr с последующей обратной итерацией) и обработки массива, извлеченного из первого столбца, результат помещается в другой массив и удаляется из конца кода. Я могу поиграться с такой функцией/решением, но у меня есть некоторые сомнения, что это будет быстрее, чем использование RegEx...
вы правы, и использование regex function внутри массива происходит очень быстро.
@Waleed Во всяком случае, ради сложности я подготовлю ответ, используя стандартный VBA. Но он (как решение RegEx) будет обрабатывать (только) строки шаблона, которые вы показываете...
«Моя цель - использовать вспомогательный столбец и установить фильтр по значению», я согласен, этот столбец можно обновить с помощью формулы, которая будет принимать значения фильтра из трех ячеек над столбцами, поэтому вы можете легко изменить параметры фильтрации. Возможные изменения в отмеченных колонках немедленно обновят вспомогательную колонку, а нажатием кнопки вы сможете снова отфильтровать строки. Код кнопки должен находиться в модуле листа, а формула — в другом модуле книги. Вы также можете установить Sub No_Filters(..) на другую кнопку, чтобы отключить фильтр.
Option Explicit
Private Sub CommandButton1_Click()
Dim lastR As Long, lastCol As Long, r As Range
Application.ScreenUpdating = False
Call No_Filters(Me)
lastR = Me.Range("A" & Me.Rows.Count).End(xlUp).Row
lastCol = Me.Cells(2, Me.Columns.Count).End(xlToLeft).Column
Set r = Me.Range(Me.Cells(2, 1), Me.Cells(lastR, lastCol))
r.AutoFilter Field:=lastCol, Criteria1: = "1"
End Sub
'Copy In a module the code below:
'this is the formula for the helper column (returns 0 or 1)
Public Function checkCrit(a As String, crita As String, b As String, critb As String, c As String, critc As String) As Integer
checkCrit = ((a Like crita) And (b Like critb) And (c Like critc)) * -1
End Function
'this is a sub to unset the filter if it is on
Public Sub No_Filters(ByRef r As Worksheet)
With r
If .FilterMode Then
.ShowAllData
End If
End With
End Sub
Строка заголовков на втором или на третьем? Я не могу понять, только глядя на ваш код...