通过使用helper列在三列上显示字符串的所有实例

Question

我使用下面的代码来显示在特定的三列上找到的字符串的所有实例。
它有一个副作用，在任何列上使用稍后的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

Answer 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

现在测试。

请也测试一下并发送一些反馈。