使用VBA在Excel中查看和隐藏列 [英] View and hide columns in excel using vba

问题描述

我有一个工作表，其值在B:G列中.在单元格A1的同一工作表中，我使用数据验证(如A，B和C)制作了一个下拉列表.

I have a worksheet with values in columns B:G. In the same sheet in cell A1 I have made a drop down list using data validation with values like A, B and C.

我需要的是，当我选择单元格值A时，则B:C列需要可见，而其他列应从D:G中隐藏.同样，如果我从列表中选择B，则需要查看D:E和B:C以及F:G列.

What I require is when I select cell value A then columns B:C need to be visible and the other columns should be hidden from D:G. In the same way if I select B from the list I need to view columns D:E and B:C and F:G should be hidden.

请您帮我一下.

注意:我对VBA并不了解.

Note: I don't have good knowledge in VBA.

推荐答案

尝试一下:

1. 打开VBA编辑器(ALT + F11)
2. 双击Sheet1
3. 在左上方的下拉菜单中选择Worksheet，在右上方的下拉菜单中选择Change
4. 粘贴此代码

1. Open the VBA editor (ALT + F11)
2. Double click Sheet1
3. Select Worksheet in the top left drop down and Change in the top right hand drop down
4. Paste this code

NB-假设数据验证位于单元格A1

NB- this assumes data validation is in cell A1

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim allColumns As Range

    Set allColumns = Columns("B:G")
    allColumns.Hidden = True

    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If Target.Value = "A" Then
            Columns("B:C").Hidden = False
        ElseIf Target.Value = "B" Then
            Columns("D:E").Hidden = False
        ElseIf Target.Value = "C" Then
            //Add more logic here
        End If
    End If
End Sub

这篇关于使用VBA在Excel中查看和隐藏列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！