Hide rows based on cell value

[mr names]

Hi - I'm fairly new to excel and I'm putting together a 'Quote' sheet.

Basically, I want to make it so that when a sales person enters a number of products in Cell C1, it unhides rows 1 at a time depending on the number.

The rows that I want to unhide are from 19 - 26

For example. if sales person selects 2 in cell C1, then 19 & 20 unhide. If they select 3 in cell C1 then 19,20,21 unhide and so on and so forth. Hope this makes sense!

Cheers!

 

[arsalan701]

Right click the sheet name and hit view code.

Then you can enter the below code in the sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("c1")
Dim n As Integer, s As Integer
n = Range("c1").Value
s = 19 + n - 1
Rows("19:26").EntireRow.Hidden = True
Rows("19:" & s & "").EntireRow.Hidden = False
'End If
End Sub


--Its very crude,Change it as required.

 

[mr names]

That works perfectly!

Thanks very much!!

 

[arsalan701]

Ur welcome!!!

 

[sycamorm]

Hi sorry for jumping in on this thread but i have a feeling that my issue is very similar and you appear to be very knowledgable.

My probklem is that i want a row to be unhidden when a cell shows "No"

i.e

Row 7 to be unhidden when cell "I:6" shows "No", if it says yes then it to unhid row 8.

Then Row 9 to be unhidden when cell "I:8" shows "No", if it says yes then it to unhid row 10. etc

if "I:6" is blank then the other rows are all hidden

i really hope this makes sense


Thanks in advance


Marc.

 

[arsalan701]

Hard to understand!

 

[sycamorm]

i have copied a template of the table below

<TABLE style="WIDTH: 271pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=360 border=0 x:str><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 5pt; mso-width-source: userset; mso-width-alt: 219" width=6><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 74pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=98 height=21 x:num>1</TD><TD class=xl36 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=134 colSpan=3>a</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl37 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" width=64>Yes</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl38 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: yellow" height=21 x:num>2</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=134 colSpan=3>b</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl39 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>No</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>What and why</TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=134 colSpan=3> </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21 x:num>3</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=134 colSpan=3>c</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl42 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Yes</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl41 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21 x:num>4</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=134 colSpan=3>d</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>No</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl44 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>What and why</TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=134 colSpan=3> </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD></TR></TBODY></TABLE>

When the answer to Que 1 is a “yes” the next row is hidden. It then shows up Que 2.(on Row 3). If Que 2 was a "no" then it shows the hidden row which is after more information from the User.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
So the response from 1 will be open row 3 and keep row 2 hidden. <o></o>
The response from row 3 will be to open both row 4 and 5 due to the no answer.<o></o>
<o></o>
I hope this is helping. I guess due to looking at the document for such a long time it is just a blur at the moment.<o></o>
<o></o>
Thanks

Marc<o></o>
<o> </o>
<o> </o>

 

[arsalan701]

I think you are trying to create a questionnaire but I suggest you use a differnt approach rather than hiding Coz it will be hard to program.

Try forms !

 

[sycamorm]

sorry when you say forms, what do you mean?

 

[arsalan701]

VBA Userforms!