Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

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.

2 Answers

Relevance
  • 7 years ago
    Favorite Answer

    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.

  • 6 years ago

    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.

    Source(s): excel macro hide rows based columns value: https://shortly.im/BbVkT
Still have questions? Get your answers by asking now.