根据Excel中的下拉列表隐藏行 [英] Hide rows based on Drop Down list in Excel

问题描述

这基本上是一个邮件模板，包含3种不同类型的内容.
我有一个带有组合框列表的Excel工作表. 该列表具有值1,2和3.为他们提供3种不同类型的内容. 当选择其中的任何一个.其余内容必须隐藏.

This is basically a mail template, containing 3 different types of content.
I have an excel sheet with a combo-box list. The list has values 1,2 and 3. & 3 different types of content for them. When any one of them is selected . The remaining content must be hidden.

推荐答案

假设您在Range("A1")中设置了具有不同选项的验证选项(在我的示例中为1-3).在适当的工作表模块中，放入以下代码，并根据需要进行更改.

Assuming you have validation option set in Range("A1") with different options (1-3 in my example). In the appropriate Sheet module put the following code and change it as needed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Dim rngOpt1 As Range
Dim rngOpt2 As Range
Dim rngOpt3 As Range
'you doesn't need to put rows references here
'as we deal with it later
    Set rngOpt1 = Range("b10:c15")   'first area to be hidden
    Set rngOpt2 = Range("d16:e20")   'second...
    Set rngOpt3 = Range("f21:g25")   'you can guess
If Range("A1") = 1 Then 'your validation cell
    rngOpt1.EntireRow.Hidden = False
    rngOpt2.EntireRow.Hidden = True
    rngOpt2.EntireRow.Hidden = True
ElseIf Range("A1") = 2 Then
    rngOpt1.EntireRow.Hidden = True
    rngOpt2.EntireRow.Hidden = False
    rngOpt2.EntireRow.Hidden = True
Else
    'you can do it on you own... :)
End If
End If
End Sub

每次更改A1中的值时，相应的行范围都会被隐藏. 我对它的效率并不特别自豪，但这是我的第一个主意.

Each time you change value in A1 the appropriate rows range would be hidden. I'm not specially proud of its efficiency but it was my first idea.

这篇关于根据Excel中的下拉列表隐藏行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！