excel macro to hide rows based upon all columns being zero or no value?

Here is 'toggle' type event handler that will alternately show all rows/hide rows 21-41 if cells are all '0', with each double click.

Copy the following event handler to the clipboard (highlight the entire code, right click inside the highlighted area, and 'Copy'):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _

Cancel As Boolean)

Dim i, LastRow

For i = 24 To 41

If Cells(i, "E").EntireRow.Hidden = True Then

Cells.EntireRow.Hidden = False

Target.Offset(1).Select

Exit Sub

End If

Next

For i = 24 To 41

If Application.Sum(Range("E" & i & ":" & "Q" & i)) = 0 _

And Application.CountA(Range("E" & i & ":" & "Q" & i)) = 0 Then

Cells(i, "E").EntireRow.Hidden = True

Else

Cells(i, "E").EntireRow.Hidden = False

End If

Next

Target.Offset(1).Select

End Sub

Select the worksheet containing the rows to evaluate and right click the sheet tab at the bottom.

Select 'View Code'.

Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').

Close the VBE (red button - top right).

To hide rows containing all zeroes, or all blanks, in columns E:Q, double click any cell. To show all rows double click any cell again.

This Site Might Help You.

RE:

excel macro to hide rows based upon all columns being zero or no value?

Prior to printing a financial statement I am looking for a macro to look to rows 24-41 and if any of those rows in which there is a 0, or no value in every column E:Q I want the macro to hide those rows.

In other words if all columns E:Q are all blank I wish the row to be hidden.