MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using VB to hide and unhide rows in Excel


Posted by Joe Winslow on August 13, 2001 1:38 PM

I want to make a button in Excel to hide and unhide rows using VB. For example, if I push "Hide" Rows 3-5 will be hidden, and if I push Unhide, Rows 3-5 will be shown. Thanks,
Joe


Posted by faster on August 13, 2001 1:41 PM

This will toggle hide/unhide:

Sub HideToggle()
If Rows("3:5").EntireRow.Hidden = True Then
Rows("3:5").EntireRow.Hidden = False
Else
Rows("3:5").EntireRow.Hidden = True
End If
End Sub

Posted by Joe Winslow on August 15, 2001 7:51 AM

Thanks for the reply. I am still having some problems. Here is my code:
The first sub is for the button, which should allow the user to click and the HideToggle to be activated. However, when I run the VB (as in when I push the "play" button to run the code) it hides the rows straight-away, and then when I click the button that should show the button, I get the error "Run-Time error '1004': Unable to set the hidden property of the range class." Any ideas? Thanks again!

Private Sub CommandButton1_Click()
HideToggle
End Sub


Sub HideToggle()
If Rows("3:5").EntireRow.Hidden = True Then
Rows("3:5").EntireRow.Hidden = False
Else
Rows("3:5").EntireRow.Hidden = True
End If
End Sub

Posted by faster on August 15, 2001 8:18 AM

Just assign HideToggle() to your button and remove
the call. I could not repeat your error.

Posted by Joe Winslow on August 15, 2001 8:21 AM

Sorry to seem so inept when it comes to this stuff, but how do I assign HideToggle() to the button. What's the code? I just started programming in Excel this week. Thank you so much for your help!

Posted by Joe Winslow on August 15, 2001 8:54 AM


Could you copy and paste the code you're using? Just to back-up here, to run the code I just click the "play" button, right? Okay, thanks again.

Posted by faster on August 15, 2001 10:25 AM

Paste the code into a module like you did before.
Create the button and assign the HideToggle().

Or right click an existing button and assign
HideToggle().

Posted by Joe Winslow on August 15, 2001 10:31 AM

Okay, so I put the HideToggle code into a new module. How do I assign it to the button? I feel like an idiot, because I'm sure it's obvious, but I think I'm missing something here. Thanks much.