Hide rows where all values are 0 in excel

Microsoft Excel 2003 (full product)
December 7, 2009 at 18:24:36
Specs: Windows XP

I am trying to hide all rows in an Excel tab where each column (C to H) is equal to 0.00 or 0.00%.


See More: Hide rows where all values are 0 in excel

Report •

December 8, 2009 at 09:59:52

The attached macro will hide all rows where all cells on a row from column C to column H are zero (includes 0%).

Select a cell anywhere in the range of cells you want to test and run the Macro.

If you only need this macro to work on one specific workbook, place the code in a module in that workbook.
If you want to use this macro on several workbooks, add it to a module in the hidden workbook 'Personal.xls'

Enter the VBA window by clicking Alt + f11 (The left Alt key and function key #11 at the same time)
In the Project Explorer window (usually on left), find VBAProject(Your.xls) or VBAProject(Personal.xls).
Right click on the one you want to use and select Insert then Module (not Class Module)
Double click Module1 which is under the Modules folder
Enter the code in the main window.


Option Explicit
Sub HideOnZero()
Dim rngTest As Range
Dim rngCell As Range
Dim lngStRow As Long
Dim lngEndRow As Long
Dim rngRow As Range
Dim blnZero As Boolean
Dim n As Integer

On Error GoTo ErrHnd

With ActiveSheet
    'selected cell within required range
    'set range to current region around selected cell
    Set rngTest = ActiveCell.CurrentRegion
    'get rows in range
    lngStRow = rngTest.Rows(1).Row
    lngEndRow = lngStRow + rngTest.Rows.Count - 1
    'go through each row
    For n = lngStRow To lngEndRow
        blnZero = True
        'test each cell in the row from column C to column H
        For Each rngCell In Range("C" & Format(n, "#0") & ": H" & Format(n, "#0")).Cells
            'test if cell value is zero
            If rngCell.Value <> 0 Then
                blnZero = False
            End If
            'if a non-zero cell encountered don't test this row any more
            If blnZero = False Then Exit For
            'if we get to the last cell in this row (column H)
            'then setup a range for the whole row
            If rngCell.Column = Range("C" & Format(n, "#0") & ": H" & _
                Format(n, "#0")).Columns.Count + 2 Then
                Set rngRow = rngCell.EntireRow
            End If
        Next rngCell
        'hide the row if no non-zero cells found
        If blnZero = True Then
            rngRow.Hidden = True
        End If
    Next n
End With
Exit Sub

'error handler
End Sub

If this does what you want, you can add a tollbar button and run it from that.


Report •
Related Solutions

Ask Question