Adding/hiding rows based on value in cell

[ryancella]

Hi there,

First time posting to the forum, so take it easy on me, alrighty?

I'm working on a workbook with multiple tabs. I am trying to make one of these tabs (called Services) and particularly dynamic one.

In this case, I have values in cells b15, b16, and b17. If b15 has a value of 1+ i would like to display rows 23 through 33. If b16 has a value of 1+ i would like to display rows 36 through 46. And so on...

However, if any of these cells has a value of 0, i want to hide rows 23 through 33 and 36 through 46, respectively.

I'm also trying to avoid making this a 'change event' since I would still like to ability to undo changes.

Help is appreciated.

Ryan

 

[ganix]

My efforts:

Sub showhiderows()
If Range("B15").Value >= 1 Then Rows("23:33").Hidden = False
If Range("B15").Value = 0 Then Rows("23:33").Hidden = True
If Range("B16").Value >= 1 Then Rows("36:46").Hidden = False
If Range("B16").Value = 0 Then Rows("36:46").Hidden = True
If Range("B17").Value >= 1 Then Rows("49:59").Hidden = False
If Range("B17").Value = 0 Then Rows("49:59").Hidden = True

End Sub

I wasn't sure exactly what you meant by "And so on", so I assumed you wanted B17 to correspond to rows 49:59 - i.e. leaving another gap of 3 rows.

 

[ryancella]

I have a naive reply. So where am I pasting this code? I'm assuming I need to go tools/macros/visual basic editor. Then what? Should I view the code on my specific Sheet (Services)? I'm in over my head, but any additional direction is helpful.

 

[ganix]

Click the "Developer" tab in the Ribbon (it should be on the right of "View"). If it's not there, click the Excel button (top left), then "Excel options", then check "show developer tab".

Click "visual basic" within the developer tab. Insert module. Paste the code. Then you'll be able to go back to excel and click "Macros", and it should be there.

You'll have to resave as a macro enabled workbook, though. And for that macro you'll have to run it while the Services sheet is active. (Though it's also possible to write it so that that's not the case.)