VBA实现筛选变更时自动更新工作表（隐藏/显示空值列）

完全可以实现这个需求，通过Excel的VBA宏就能完成筛选变更后自动调整列的显示状态。以下是具体步骤和代码：

实现步骤 #

1. 打开目标工作表，按下Alt + F11打开VBA编辑器。
2. 在左侧「工程资源管理器」中找到对应工作表，双击打开其代码窗口。
3. 将下方代码粘贴到窗口中，保存文件为.xlsm格式（仅该格式支持宏运行）。

VBA优化代码（适配大数据量） #

Private Sub Worksheet_Calculate()
    Dim ws As Worksheet
    Dim lastCol As Long
    Dim col As Long
    Dim visibleRange As Range
    
    Set ws = Me
    ' 获取表格最后一列的列号
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' 先取消所有列的隐藏，确保判断准确
    ws.Columns.Hidden = False
    
    ' 遍历第2列到最后一列（第1列为筛选列，始终保持显示）
    For col = 2 To lastCol
        ' 捕获无可见单元格的错误
        On Error Resume Next
        ' 获取当前列的可见数据区域（假设第1行是表头，数据从第2行开始）
        Set visibleRange = ws.Range(ws.Cells(2, col), ws.Cells(ws.Rows.Count, col).End(xlUp)).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        
        ' 统计可见区域的非空单元格数量，为0则隐藏列
        ws.Columns(col).Hidden = (Application.WorksheetFunction.CountA(visibleRange) = 0)
    Next col
End Sub

注意事项 #

• 如果你的表头不是第1行，需修改代码中ws.Cells(2, col)的数字为实际数据起始行号。
• 打开文件时需启用宏，否则代码不会生效。
• 优化后的代码用CountA函数替代逐单元格遍历，能大幅提升大数据量下的运行效率。

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