时间戳

首页 > 解决方案 > 根据目标单元格中​​的条件隐藏和显示范围

excel - 根据目标单元格中​​的条件隐藏和显示范围

问题描述

我有一个显示一年中月份的下拉列表，我正在尝试根据选择隐藏列。

这就是我所拥有的，但我无法让它工作。我是新手，所以我的知识非常有限。

Sub Month_Change(ByVal Target As Range)
    ActiveSheet.Activate
    If Not Application.Intersect(Range("A1"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
        Case Is = "January": Columns("FF:GD").EntireColumn.Hidden = True
                             Columns("B:FE,GE:XFD").EntireColumn.Hidden = False
        Case Is = "February": Columns("GF:HB").EntireColumn.Hidden = False
                              Columns("B:GE,HC:XFD").EntireColumn.Hidden = True
        Case Is = "March": Columns("HD:IF").EntireColumn.Hidden = False
                           Columns("B:HC,IG:XFD").EntireColumn.Hidden = True
        Case Is = "April": Columns("JE:JI").EntireColumn.Hidden = False
                           Columns("B:JD,JJ:XFD").EntireColumn.Hidden = True
        Case Is = "May": Columns("JE:JI").EntireColumn.Hidden = False
                         Columns("B:JD,JJ:XFD").EntireColumn.Hidden = True
        Case Is = "June": Columns("JE:JI").EntireColumn.Hidden = False
                          Columns("B:JD,JJ:XFD").EntireColumn.Hidden = True
        Case Is = "July": Columns("B:AD").EntireColumn.Hidden = False
                          Columns("AE:XFD").EntireColumn.Hidden = True
        Case Is = "August": Columns("AF:BD").EntireColumn.Hidden = False
                            Columns("B:AE,BE:XFD").EntireColumn.Hidden = True
        Case Is = "September": Columns("BE:CD").EntireColumn.Hidden = False
                               Columns("B:BD,CE:XFD").EntireColumn.Hidden = True
        Case Is = "October": Columns("CE:DD").EntireColumn.Hidden = False
                             Columns("B:CD,DE:XFD").EntireColumn.Hidden = True
        Case Is = "November": Columns("DF:ED").EntireColumn.Hidden = False
                              Columns("B:DE,EE:XFD").EntireColumn.Hidden = True
        Case Is = "December": Columns("EE:FE").EntireColumn.Hidden = False
                              Columns("B:ED,FF:XFD").EntireColumn.Hidden = True
        End Select
    End If
End Sub

我的电子表格

标签： excelvba

解决方案

我认为你让你的任务比它需要的更复杂。如果我有一个类似的项目，我会这样做：

命名范围

同时Row1，突出显示包含一月份所有列和名称框类型的单元格January。对 重复相同的操作February，March依此类推——直到并包括December。

私人子

根据@Rory 的建议，Private Sub通过范围名称引用它们来创建“自动”隐藏/取消隐藏相应的列。以下代码将为您完成 - 只要您正确命名了范围：

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo GetOut
Application.EnableEvents = False

Dim MyMonth As String

MyMonth = ActiveSheet.Range("A1").Value  '<-- It looks like your month of choice is in cell A1

If Not Intersect(Range("A1"), Target) Is Nothing Then
    ActiveSheet.Range("B1:XFD1").EntireColumn.Hidden = True  '<-- Hide all columns in one step
    ActiveSheet.Range(MyMonth).EntireColumn.Hidden = False   '<-- Unhide the month chosen in A1
End If

GetOut:
    Application.EnableEvents = True
    Exit Sub

End Sub

推荐阅读