CODEWITHSUNDEEP
excelexcel-formulavba
Its not blank
Its not blank

Reputation: 3095

Hide rows based on Drop Down list in Excel

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.

Upvotes: 1

Views: 3990

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

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

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.

Upvotes: 3

Related Questions