Unhiding rows as a result of selection in dropdown list

[guz]

How can I set something up that when a value from a dropdown list is selected
then a range of rows are unhidden then when another value is selected a
different range is unhidden?

 

[Dave Peterson]

If you're using data|validation, you can use a macro:

If you want to try...

Rightclick on the worksheet tab that should have this behavior. Select View
code and paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then
Exit Sub 'single cell at a time
End If

If Intersect(Target, Me.Range("a1")) Is Nothing Then
Exit Sub
End If

Me.Rows.Hidden = False
Select Case LCase(Target.Value)
Case Is = LCase("a")
Me.Range("a2").Resize(12).EntireRow.Hidden = True
Case Is = LCase("b")
Me.Range("a22").Resize(7).EntireRow.Hidden = True
End Select

End Sub

My test routine checks for a change in A1. If it sees "a", then it hides rows 2
to 13 (resizes to 12 rows). If it sees "b", it hides rows 22 to 28 (resizes to
7 rows).

It also unhides all the rows before it checks what changed.

 

[guz]

Thanks Dave, this'll solve my problem

If you're using data|validation, you can use a macro:

If you want to try...

Rightclick on the worksheet tab that should have this behavior. Select View
code and paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then
Exit Sub 'single cell at a time
End If

If Intersect(Target, Me.Range("a1")) Is Nothing Then
Exit Sub
End If

Me.Rows.Hidden = False
Select Case LCase(Target.Value)
Case Is = LCase("a")
Me.Range("a2").Resize(12).EntireRow.Hidden = True
Case Is = LCase("b")
Me.Range("a22").Resize(7).EntireRow.Hidden = True
End Select

End Sub

My test routine checks for a change in A1. If it sees "a", then it hides rows 2
to 13 (resizes to 12 rows). If it sees "b", it hides rows 22 to 28 (resizes to
7 rows).

It also unhides all the rows before it checks what changed.

 

[guz]

I've tried the solution given and it works when I manually input the values.
However a) I want the macro to work from the value selected in the dropdown
list
b) To further complicate matters I would want something similar with a
second dropdown!
alan g

 

[Dave Peterson]

How did you create the dropdown?

Was it data|Validation?
Was it a dropdown from the Forms toolbar?
Was it a combobox from the control toolbox toolbar?

What are the addresses of the cells with data|validation?
What are the names of the dropdowns?
What are the names of the comboboxes?

What version of excel are you using?

 

[guz]

I've solved part a) of my follow-up query, however I can't get part b) to
work i.e. how can I replicate the row unhide for a value for a secondary drop
down in the macro.

To reply to your questions (in reverse order)
Excel 2003 (SP3)
Cell B3 is validated through data validation with a source = changetype
which is a list held on a separate sheet containing the values (New,
Amendment and Update)

Cell E3 is validated through data validation with a source = system which is
a list held on a separate sheet containing the values (Systema, Systemb,
Systemc)

 

[Dave Peterson]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then
Exit Sub 'single cell at a time
End If

If Not (Intersect(Target, Me.Range("b3")) Is Nothing) Then
'changing B3
Me.Rows.Hidden = False
Select Case LCase(Target.Value)
Case Is = LCase("New")
Me.Range("a2").Resize(12).EntireRow.Hidden = True
Case Is = LCase("Amendment")
Me.Range("a22").Resize(7).EntireRow.Hidden = True
Case Is = LCase("Update")
Me.Range("a22").Resize(7).EntireRow.Hidden = True
End Select
ElseIf Not (Intersect(Target, Me.Range("e3")) Is Nothing) Then
'changing e3
'your code that does other stuff here
'if you're changing another cell
'do this surrounding the change
Application.EnableEvents = False
Me.Range("F3").Value = "changed to something else"
Application.EnableEvents = True
'to stop your change via code from firing this event
End If

End Sub

I've solved part a) of my follow-up query, however I can't get part b) to
work i.e. how can I replicate the row unhide for a value for a secondary drop
down in the macro.

To reply to your questions (in reverse order)
Excel 2003 (SP3)
Cell B3 is validated through data validation with a source = changetype
which is a list held on a separate sheet containing the values (New,
Amendment and Update)

Cell E3 is validated through data validation with a source = system which is
a list held on a separate sheet containing the values (Systema, Systemb,
Systemc)

 

[guz]

Great! This now does exactly what I want. Thanks.