Показать все экземпляры строки в трех столбцах с помощью вспомогательного столбца
Я использую приведенный ниже код, чтобы показать все экземпляры строки, найденные в определенных трех столбцах.
у него есть побочный эффект: при использовании позже autofiletr в любом столбце все скрытые строки отображаются снова.
Я ищу ответ во вспомогательной колонке, чтобы решить эту проблему.
Заранее спасибо за любую помощь.
Sub Show_all_instances_on_three_columns()
Dim ws As Worksheet, lRow As Long, lcol_n As Long, lastcol As String
Dim rng As Range, i As Long, crit_Filter_Three_Columns As String
Set ws = ActiveSheet
lRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'Last_Row on Column "A"
lcol_n = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column 'Last_Column number on Row 2
lastcol = Split(Cells(1, lcol_n).Address(True, False), "$")(0) 'Letter of Last_Column
Set rng = ws.Range("A2:" & lastcol & LRow) 'Source Range to apply Filter on it
If Not ws.AutoFilterMode Then rng.AutoFilter 'Set AutoFilter if not already set
ws.AutoFilter.ShowAllData
crit_Filter_Three_Columns = "*test*"
rng.AutoFilter Field:=6, Criteria1:=crit_Filter_Three_Columns, Operator:=xlFilterValues
For i = 1 To rng.Rows.Count
If rng.Cells(i, 3).Value Like crit_Filter_Three_Columns Then rng.Rows(i).Hidden = False 'Unhide according to the column_3
If rng.Cells(i, 7).Value Like crit_Filter_Three_Columns Then rng.Rows(i).Hidden = False 'Unhide according to the column_7
Next i
End Sub
Один вопрос не так ясен: вам нужно «точное совпадение 'crit_Filter_Three_Columns'» или «точное совпадение 'crit_Filter_Three_Columns' должно содержаться в строках трех столбцов?
@FaneDuru, нет точного совпадения crit_Filter_Three_Columns, например "*test*"
Хорошо, я пропустил подстановочные знаки... На самом деле, я вижу, что вы изменили его после того, как я прокомментировал.
Ответы 1
Пожалуйста, попробуйте следующий адаптированный код:
Sub Show_all_instances_on_three_columns()
Dim ws As Worksheet, lRow As Long, lcol_n As Long, lastcol As String
Dim rng As Range, i As Long, crit_Filter_Three_Columns As String
Dim arr, arrH, hlpCol As Long, rngHelp As Range
Const helpCol As String = "HelpCol"
Set ws = ActiveSheet
lRow = ws.cells(ws.rows.count, "A").End(xlUp).row 'Last_Row on Column "A"
lcol_n = ws.cells(2, ws.Columns.count).End(xlToLeft).column 'Last_Column number on Row 2
lastcol = Split(cells(1, lcol_n).address(True, False), "$")(0) 'Letter of Last_Column
'helper column number (first empty one)
Set rng = ws.Range("A2:" & lastcol & lRow) 'Source Range to apply Filter on it
crit_Filter_Three_Columns = "*test*"
'determine the helper column and extend (or not the headers range):
Set rngHelp = ws.rows(2).Find(what:=helpCol, LookIn:=xlValues, Lookat:=xlWhole)
If Not rngHelp Is Nothing Then
hlpCol = rngHelp.column
Else
hlpCol = lcol_n + 1
ws.cells(2, hlpCol).Value = helpCol
Set rng = rng.Resize(, rng.Columns.count + 1)
End If
If Not ws.AutoFilterMode Then rng.AutoFilter 'autofilter the resized range
ws.AutoFilter.ShowAllData
arr = rng.Value2 'place the range in an array for faster processing
ReDim arrH(1 To UBound(arr) - 1, 1 To 1) 'redim the array to keep the helper character ("H")
For i = 2 To UBound(arr)
If arr(i, 3) Like crit_Filter_Three_Columns Or _
arr(i, 6) Like crit_Filter_Three_Columns Or _
arr(i, 7) Like crit_Filter_Three_Columns Then
arrH(i - 1, 1) = "H"
End If
Next i
'drop the helper array (arrH) content in the appropriate column:
ws.cells(2, hlpCol).Offset(1).Resize(UBound(arrH), 1).Value2 = arrH
'Filter the range by helper column, for "H":
rng.AutoFilter field:=hlpCol, Criteria1: = "H", Operator:=xlFilterValues
End Sub
Проверено сейчас.
Пожалуйста, также протестируйте его и отправьте отзыв.
@Waleed Меня тоже, но мне все равно. Не тестировать/проверять код вообще, могут быть опечатки или другие ошибки. Я только хотел доставить кусок кода, чтобы быть хотя бы подсказкой для вас. Я не знал, что вы тоже уйдете из офиса... Сейчас протестирую, исправлю и обновлю через несколько минут...
@Waleed В предыдущем непроверенном коде я нашел опечатку, двойное (i) объявление и немного изменил логику кода с точки зрения изменения размера диапазона заголовков для фильтрации, существует ли вспомогательный столбец или нет...
Теперь он работает отлично. У меня было некоторое время, чтобы понять эту часть (используя -1) ReDim arrH(1 To UBound(arr) - 1, 1 To 1), и позже я перешел на ReDim arrH(2 To UBound(arr), 1 To 1) и arrH(i, 1) = "H", и это также работает и мне легче понять.
Также, пожалуйста, есть ли разница между использованием LookIn:=xlValues и LookIn:=xlFormulas? Я вспомнил, что вы ранее говорили, что всегда используете xlFormulas.
@Waleed Это -1 было выбрано, потому что содержимое массива начало загружаться со второй строки, а содержимое массива отбрасывалось, начиная с третьей ... Когда я сказал, что «всегда используется xlFormulas», я строго имел в виду случай использования Find для определения последнего столбца, даже если он скрыт... В противном случае xlValues — это именно то, что AutoFilter нужно для правильного возврата того, что вы хотите.
мой последний (удаленный) комментарий был здесь, а не по другому вопросу. Я нашел ответ очень простым, поэтому я думал, что вы отказались отвечать (извините). Во всяком случае, я мог бы добавить подстановочные знаки к строкам, используя Split и позже Join, а затем добавить "*" в начале и в конце textbox.value.
@Waleed Я не помню точное значение вашего комментария, но я думаю, что нет необходимости сначала разделять, а затем «добавлять». Вы должны просто заменить пробелы подстановочными знаками (используя Replace), а также добавить их к окончаниям строк... Думаю, в одной строке кода. Но говоря о "textbox.value", я думаю о другом вопросе... И проблема в том, что массив, собранный таким образом, не будет принят Autofilter, который принимает только две такие строки, содержащие подстановочные знаки.
@Waleed Но это способ сделать соответствующий массив, способный работать на AutoFilter, даже там. По-другому...
Я не уверен, что правильно понял ваш вопрос. Таким образом, вы хотите видеть все строки, содержащие в столбцах C, F и G строки, содержащие «crit_Filter_Three_Columns». Содержит из-за
Likeиспользования... Будет ли такое понимание правильным тоном? И, как метод, создать вспомогательную колонку и, наконец, отфильтровать по ней. Это то, что тебе надо? Или проверить точное совпадение 'crit_Filter_Three_Columns'?