我使用下面的代码来显示在特定的三列上找到的字符串的所有实例。
它有一个副作用,在任何列上使用稍后的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
请尝试下一个改编代码:
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
现在测试。
请也测试一下并发送一些反馈。