借助辅助列实现三列字符串实例全显示并解决AutoFilter副作用

原代码的问题在于同时混用了AutoFilter过滤和手动设置行隐藏两种机制——Excel的AutoFilter在执行ShowAllData时会自动取消所有手动隐藏的行，导致后续过滤操作出现异常。用辅助列统一通过AutoFilter控制行显示，就能彻底解决这个冲突。

实现思路 #

1. 在数据区域末尾添加辅助列，标记每行是否在目标列（第3、6、7列）中包含指定字符串；
2. 利用公式快速填充辅助列的判断结果；
3. 基于辅助列执行AutoFilter，只显示符合条件的行；
4. 所有行的显示/隐藏完全由AutoFilter控制，避免手动隐藏带来的冲突。

修改后的VBA代码 #

Sub Show_all_instances_on_three_columns()
    Dim ws As Worksheet
    Dim lRow As Long, lastCol As Long, helperCol As Long
    Dim rng As Range, helperRng As Range
    Dim targetStr As String
    
    ' 初始化变量
    Set ws = ActiveSheet
    targetStr = "*test*" ' 替换为你需要匹配的字符串
    
    ' 获取数据区域的边界
    lRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column
    helperCol = lastCol + 1 ' 辅助列放在现有最后一列之后
    
    ' 关闭现有自动筛选，避免冲突
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    
    ' 设置辅助列标题
    ws.Cells(1, helperCol).Value = "匹配标记"
    
    ' 填充辅助列：判断第3、6、7列是否包含目标字符串
    Set helperRng = ws.Range(ws.Cells(2, helperCol), ws.Cells(lRow, helperCol))
    ' SEARCH不区分大小写，要区分的话替换为FIND
    helperRng.Formula = "=OR(ISNUMBER(SEARCH(""test"",C2)),ISNUMBER(SEARCH(""test"",F2)),ISNUMBER(SEARCH(""test"",G2)))"
    ' 将公式转换为值，避免后续数据变动影响筛选状态
    helperRng.Value = helperRng.Value
    
    ' 定义包含辅助列的完整数据区域
    Set rng = ws.Range("A1:" & ws.Cells(lRow, helperCol).Address(False, False))
    
    ' 基于辅助列筛选，只显示符合条件的行
    rng.AutoFilter Field:=helperCol, Criteria1:=True
    
    ' 可选：如果不需要保留辅助列，取消下面的注释即可
    ' ws.Columns(helperCol).Delete
End Sub

关键说明 #

• 匹配规则：代码中用SEARCH实现不区分大小写的模糊匹配，若需要区分大小写，替换为FIND即可；
• 动态字符串：如果需要每次运行时输入目标字符串，可将targetStr = "*test*"替换为targetStr = "*" & InputBox("请输入要匹配的内容：") & "*"；
• 辅助列处理：如果不想保留辅助列，打开代码末尾的ws.Columns(helperCol).Delete注释，筛选完成后自动删除辅助列；
• 兼容性：全程用AutoFilter控制行显示，后续再执行任何AutoFilter操作时，不会出现隐藏行意外显示的问题。

内容的提问来源于stack exchange，提问作者Waleed