时间戳

首页 > 解决方案 > Excel VBA过滤合并单元格

excel - Excel VBA过滤合并单元格

问题描述

我有 5 个变量的 Excel 表：考试、月份（一月、二月、三月、四月）。每个月都有 3 个变量：低、中、高。而且我希望 VBA 代码进行过滤以仅显示所有月份的“高”列。我的代码工作正常，但是当我合并几个月的单元格时它不起作用：

Sub filter_high()

Columns("B:C").Select
Selection.EntireColumn.Hidden = True
Columns("E:F").Select
Selection.EntireColumn.Hidden = True
Columns("H:I").Select
Selection.EntireColumn.Hidden = True
Columns("K:L").Select
Selection.EntireColumn.Hidden = True

End Sub

在此处输入图像描述

标签： excelvbafilter

解决方案

您可以设置列的宽度，效果与可见关闭相同

Option Explicit

Sub showMediumOnly()
    Dim arr(2) As String
    Dim c As Integer
    Dim item As Variant
    
    arr(0) = "Low"
    arr(1) = "High"
    
    For c = 1 To ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column + 2  '  loop trought columns +2 cuz merget columns
        For Each item In arr    '   checking array
            If ActiveSheet.Cells(2, c) = item And ActiveSheet.Cells(2, c) <> "" Then ActiveSheet.Columns(c).ColumnWidth = 0 '   condition to check if its target array and change width
        Next
    Next
End Sub

推荐阅读