Need macro to hide rows only where there is text in the Cell in Column B AND the values in columns C through P are zero. in XL2007.

[pdawson]

I'm trying to write a macro that will hide all rows where there is text in column B AND where the values in columns C through P are zero (not where the values sum to zero, but where each cell in those columns is zero).

I've written a macro that will hide the rows where the values in the cells in a given row in columns C through P are zero, but I'm having trouble adding in the second condition (checking if there is text in column B as well). Basically I have a spreadsheet with about 950 line items, where many of the rows have zeroes associated with a particular line item. But the line items fall into categories (such as personnel, programming, etc.) and these categories are separated by completely blank/empty rows. I don't want the macro to hide these empty rows, just the ones where there is a line item in column B and only values of zero in columns C through P. Make sense?

I'm using Excel 2007.

Here is what I have written so far, which just takes care of checking and hiding rows with values of zero in Columns C through P.

Code:

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

Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

With ActiveSheet
'selected cell within required range
'set range to current region around selected cell
Set rngTest = Range("C9:U961")
'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 P
For Each rngCell In Range("C" & Format(n, "#0") & ": P" & 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 P)
'then setup a range for the whole row
If rngCell.Column = Range("C" & Format(n, "#0") & ": P" & _
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
ErrHnd:
Err.Clear
End Sub

 

[tweedle]

I think this should work in your code to test col B

If Len(.Cells(n, "B").Text) then

This would be my version of the routine:

Option Explicit
Sub HideZeros()
    Dim i, fr, lr As Long
    Dim mathrng As Range
    On Error GoTo ErrHnd
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False
    With ActiveSheet
        fr = 9
        lr = .Cells(.Rows.Count, 2).End(xlUp).Row
        For i = lr To fr Step -1
            Set mathrng = Range(.Cells(i, "C").Address & ":" & .Cells(i, "P").Address)
            mathrng.Select
            If Len(.Cells(i, "B").Text) And _
               WorksheetFunction.Sum(mathrng) = 0 Then
                mathrng.Rows(1).EntireRow.Hidden = True
            End If
        Next
    End With
ErrHnd:
    Debug.Print "Row: "; i, Err.Number, Err.Description
    Err.Clear
    Application.EnableEvents = True
End Sub

 

[pdawson]

Tweedle,

it works beautifully except for one thing: it will hide a row that has values in the cells if those values offset each other and sum to zero. For example, in my sheet cell F493 has a value of $220,953 and H493 has a value of negative (-) $220,953. Because these sum to zero, the row gets hidden by the macro. But I want that row to be visible. Meaning the macro needs not to check each row as a whole, but to check each cell to see if it's greater than or less than 0, and if one or more cells in a row is greater than or less than 0, that row should remain unhidden.

Any suggestions?

 

[Trebor76]

For reference, I have wasted my time also answering this thread here