excel vba toggle hide/unhide range rows across multiple sheets, if cell value 0

Question

i would like the below work across sheets "Summary", "W1", "W2", "W3","W4","W5" but I am facing some issues, can you please help.

The code is to hide empty rows across the above sheets, to hide unused rows. The above ranges are identical across all of the above sheets. The button is to toggle hide/unhide rows.

Thank you!

link to my file: my workbook

Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Less Rows" Then
CommandButton1.Caption = "More Rows"
Else
CommandButton1.Caption = "Less Rows"
End If
On Error Resume Next
  With Range("a8:a91,a96:a121").SpecialCells(xlBlanks).EntireRow
    .Hidden = Not .Hidden
  End With
  On Error GoTo 0
End Sub

Are you just asking how to loop between all the sheets? Does the code above give you an error? Does it do something you don't expect? What issues are you facing? — BruceWayne
It works great but only on the Summary sheet. I would like to use one button to perform same action across all the sheets I've mentioned. But on all other sheets i have a formula, and Xlblanks does not work. That's why I need sheets in the code and cell value (i think). But I don't know how to do it yet. — Anna Zet
What defines an unused row? If there's NO data at all, in any cell in the row? Or is it mainly like, if Column B is empty, that's an "unused row" for your purposes? — BruceWayne
Sheet "summary" a blank row is a row with no data. On Sheets W1-W5 it will be a formula returning "". — Anna Zet
Try checking values of the cell. xlBlanks check the content of the cell which can be a formula (it's not blank). — M--

M-- M-- · Accepted Answer · 2017-04-17T17:35:15

This loops over all the rows in the range and checks for the values and also loop over all the sheets in the workbook.

Private Sub CommandButton1_Click()

Dim rng As Range
Dim iRow as Range
Dim wsh As Worksheet
Dim hidden_status As Boolean

If CommandButton1.Caption = "Less Rows" Then
   CommandButton1.Caption = "More Rows"
Else
   CommandButton1.Caption = "Less Rows"
End If

On Error Resume Next

For Each wsh in ThisWorkbook.Worksheets
   Set rng = wsh.Range("A8:A91,A96:A121") 
    For Each iRow in rng.Rows
        If iRow.Value = "" Then
           With iRow.EntireRow
                 hidden_status = .Hidden
                .Hidden = Not hidden_status
           End With
        End If
    Next iRow
Next wsh

On Error GoTo 0

End Sub

excel vba toggle hide/unhide range rows across multiple sheets, if cell value 0

1 Answers