conditionally hiding rows

[Derrick]

can anyone help me figure out this problem?
I have an drop down option list, and i would like to create an if statement
that will hide select rows depending on which option is selected:
ie if option A is checked,
then rows 23,24, 26 are hidden.
if option b is checked,
rows 25, 27,28 are hidden
and if option c is checked
rows 21 22 29 are hidden.
simple enough i hope?

thanks!

 

[Bernard Liengme]

I will assume the dropdown box relates to cell A1 on Sheet1
Right click the Sheet1 tab; use View code and paste this subroutine

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

Rows("22:29").EntireRow.Hidden = False
If Range("A1") = "A" Then
Rows("23:24").EntireRow.Hidden = True
Rows("26:26").EntireRow.Hidden = True
ElseIf Range("A1") = "B" Then
Rows("25:25").EntireRow.Hidden = True
Rows("27:28").EntireRow.Hidden = True
ElseIf Range("A1") = "C" Then
Rows("21:22").EntireRow.Hidden = True
Rows("29:29").EntireRow.Hidden = True
End If
End Sub

Whenever the value inA1 changes, the subroutine will run
Just alter "A1" to suit your needs
best wishes

 

[Don Guillett]

Bernards should do it

 

[Derrick]

Right now, you surpass Batman on my list of awesome superheros.

Thank you!

 

[Bernard Liengme]

Great feedback! I'm off to buy a large-sized hat

 

[Don Guillett]

Don't forget the cape

 

[Michele]

I'll throw in the utility belt if you can show me how to modify the code to
target a separate worksheet/cell within my workbook...worksheet values are
name="Change Form" range=E5

 

[Don Guillett]

Wanna try explaining this again.

 

[Michele]

Let me try...

I'm active on Sheet2, and want to hide rows in Sheet2 based on the dropdown
selection on Sheet1 cell K1.

I added the code below to Sheet1 with the dropdown in Sheet 1 cell K1 and
when I used the dropdown it worked slick.

I then added the code to Sheet2 (modifying the range to b1), but then I used
a formula in Sheet 2 cell b1 to pull the value of the dropdown on Sheet1 cell
k1 I got nothing. I'm guessing it's not reading the off sheet reference?

Thanks~
Michele

 

[Don Guillett]

sheet EVENT code must be in the sheet it is trying to change
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.