如何实现Excel能力矩阵按部门动态显示非0值列？

针对原静态VBA代码无法适配新增列、跨部门培训场景的问题，以下是动态判断列显示状态的解决方案：通过检查筛选后当前部门可见行中各能力列是否存在大于0的值，自动控制列的显示/隐藏。

改进后的动态VBA代码 #

Public Sub Hide_Department_Columns_Dynamic(dataSheet As Worksheet)
    Dim visibleRange As Range
    Dim checkColumns As Range
    Dim col As Range
    Dim maxVal As Variant
    
    ' 关闭屏幕更新，提升运行效率
    Application.ScreenUpdating = False
    
    With dataSheet
        ' 先取消所有列的隐藏，确保初始状态正确
        .Columns.Hidden = False
        
        ' 检查工作表是否开启了自动筛选
        If .AutoFilterMode Then
            On Error Resume Next
            ' 获取筛选后的可见数据行（排除表头行）
            Set visibleRange = .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            
            ' 指定需要检查的能力列范围（可根据实际需求调整）
            Set checkColumns = .Range("AB:CD")
            
            ' 遍历每一列，判断是否需要隐藏
            For Each col In checkColumns.Columns
                ' 计算该列在可见行中的最大值
                On Error Resume Next
                maxVal = WorksheetFunction.Max(Intersect(col, visibleRange))
                On Error GoTo 0
                
                ' 若最大值<=0，说明该部门无员工掌握此能力，隐藏列；反之显示
                col.EntireColumn.Hidden = (maxVal <= 0)
            Next col
        End If
    End With
    
    ' 恢复屏幕更新
    Application.ScreenUpdating = True
End Sub

代码关键说明 #

• 动态感知筛选结果：通过SpecialCells(xlCellTypeVisible)自动获取筛选后的可见数据行，无需硬编码部门名称或行号。
• 适配新增列：只需调整checkColumns的范围（如扩展到CE:CF），新增的能力列会自动纳入判断逻辑。
• 容错处理：加入错误捕获机制，避免因筛选后无可见数据（如无匹配结果）导致代码报错。
• 效率优化：关闭屏幕更新减少界面闪烁，提升宏的运行速度。

自动触发设置 #

若需要筛选后自动调整列显示，可在目标工作表的代码模块中添加以下事件触发代码：

Private Sub Worksheet_Calculate()
    ' 每次工作表计算（包括筛选操作）时自动执行动态列调整
    Hide_Department_Columns_Dynamic Me
End Sub

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