Code to hide rows based on cell contents
 

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

Code to hide rows based on cell contents
 

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

-----Original Message-----
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
.

Code to hide rows based on cell contents
 

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?

-----Original Message-----
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

-----Original Message-----
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
.

.

Code to hide rows based on cell contents
 

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


"Tim" wrote in message
...
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?

-----Original Message-----
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

-----Original Message-----
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
.

.

Code to hide rows based on cell contents
 

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

-----Original Message-----
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


"Tim" wrote in
message
...
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?

-----Original Message-----
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

-----Original Message-----
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
.

.



.

Code to hide rows based on cell contents
 

Sorry - ignore me, its wworking.

Thank you mighty clever dudes of the collective info
space.