Using Toggle Button to Hide/Unhide blank rows

[gboudreau]

Ok, I have searched the forum, tried using Excel Help but just can't quite figure it out. What I have created is a project management timeline spreadsheet. what i want to do is use a toggle button to hide and unhide any rows that do not contain any information in column A. sometimes not all the rows will be used this depends on the amount of task for the project.

The following is what I have so far but just cant figure out the rest. If I click more than once ALL rows are hidden.

Private Sub ToggleButton1_Click()

Dim myRg As Range
'Change the [A1], [A65536] to your range
Set myRg = Range([A1].End(xlDown), [A80].End(xlUp))
On Error Resume Next
Set myRg = myRg.SpecialCells(4)
If Err = 0 Then
myRg.EntireRow.Hidden = True
Else
MsgBox "No blanks"
End If
Set myRg = Nothing

End Sub



Additionally, once I have this toggle working I would like to be able to protect the sheet and have the toggle still work.

Any ideas?

Thanks

 

[Shaggy86]

Then you can use this simple code to toggle visibility of the rows where the cells in range A19:A100 are blank...

Sub HideUnhideRowsIfColumnAisBlank()
  On Error Resume Next
  With Range("A19:A100").SpecialCells(xlBlanks).EntireRow
    .Hidden = Not .Hidden
  End With
  On Error GoTo 0
End Sub

Hello there I have a question about this Macro. Can you help me with improved version of it?

I want to hide/show the Rows B2-AZ2 and the Columns(B3:AZ123) under the said Rows if the Columns B3:AZ123 are empty.

I use Column A for names so I cannot use this useful Macro. I guess that I need also second parameter that specifies second Range and I does not know how to implement it. I never worked with Macros before and I really need it. Also any tips for starters would be nice as well. Thank you for your help.

 

[merquiaga]

Then you can use this simple code to toggle visibility of the rows where the cells in range A19:A100 are blank...

Sub HideUnhideRowsIfColumnAisBlank()
  On Error Resume Next
  With Range("A19:A100").SpecialCells(xlBlanks).EntireRow
    .Hidden = Not .Hidden
  End With
  On Error GoTo 0
End Sub

Thank you for this!!!!!