Using VBA to automatically hide/unhide entire rows

[Milos]

Hi all,

I am currently making a spreadsheet that needs to hide 30 rows when a certain condition has been met.

Does anyone know how to design a macro that can automatically hide/unhide 30 rows (rows 45 to 75) in the first specific worksheet "Sheet 1". The condition that will be met is when either a "Yes" or a "No" is entered into a specific cell (A3) or I could alternatively link it to a command button or combo box.

Thanks,

 

[Peter_SSs]

Assuming the Cell A3 that you are talking about is also on that same sheet where the rows will be hidden/unhidden then try this.
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Private Sub Worksheet_Change(ByVal Target As Range)
  Rows("45:75").Hidden = LCase(Range("A3").Value) = "yes"
End Sub

It will hide the rows if you type "Yes" (or yes, YES etc) into the cell otherwise it will unhide them.