Code to hide rows based on cell contents

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

Hi

I have a cost model, you have to answer a set of
questions. The set of questions depends on which of 3
options is selected in a cell (i've used a data
validation list).
I'm new to VB in Excel and I need something like this:

If Selection Cell = option A
Hide Rows 6-9
If Selection Cell = option B
Hide Rows 10-18
Else
Show all of them

Any suggestions?
TIA
tim
 
Hi Tim

Trye this:-

Private Sub Worksheet_Change(ByVal Target As Range)

Rows("6:18").EntireRow.Hidden = False

If Range("a1") = "a" Then
Rows("6:9").EntireRow.Hidden = True
ElseIf Range("a1") = "b" Then
Rows("10:18").EntireRow.Hidden = True
End If

End Sub
 
Thanks for that

I've pasted the code it....but how does it fire?
Selecting the cell does not invoke the code? I've missed
something, but I dont know what!

How do I include the event to trigger the code? I need a
sort of 'on value change' event, something like that.
Any suggestions?
 
The Change event is triggered when the value of a cell is
changed, not when a cell is selected. Moreover, make sure you
pasted the code in the code module for the appropriate worksheet,
not in to a general code module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
well, the code I've got reads:
Private Sub Worksheet_Change(ByVal Target As Range)

Rows("6:18").EntireRow.Hidden = False

If Range("c5") = "AMI Standalone" Then
Rows("13:18").EntireRow.Hidden = True
ElseIf Range("c5") = "USD Standalone" Then
Rows("20:37").EntireRow.Hidden = True
End If

End Sub
--------------but changing the cell is not firing it -
any more thoughts?
THanks again
tim
 
Sorry - ignore me, its wworking.

Thank you mighty clever dudes of the collective info
space.
 
Back
Top