Excel 2007 : Hiding rows based on values in cells

Hello,

I have been trying to find a solution to the following problem since yesterday. So I finally came up with this code:

Sub Worksheet_Hide()
Dim lastRow As Long, myRange As Range
Application.Calculation = xlCalculationManual
lastRow = Cells(Rows.Count, 2).End(xlUp).Row
Rows.Hidden = False
For Each myRange In Range("g11:r62")
If myRange.Value = 0 Then myRange.EntireRow.Hidden = True
Next myRange
End Sub

Here is my problem. The range of the table that I need to check is g11:r62. If any cell in that range is populated then I don't want the row collapsed, but if every cell in the row is zero, then I would like it to be collapsed. As you can see now from looking at the code, it collapses everything. Every cell is hidden if any cell is empty.

Thanks, but that code just hides rows 5 and 6. I need it to hide rows 12-62, if any cell in the range of g12:r62 is blank.

Example: if there is a 0 value in all cells g12:r12, then I need it to hide row 12. However, if even one cell has a non-0 value, for example m12, then I need the row to show. Hope that clears it up a little.

Ah, sorry about that. I'll make sure I post my code appropriately in the future.

Here is one worksheet from the book. Both of the codes don't work. The first one just hides two rows, and the "amended code" just hides every row.

Again as you look at the sheet, I want to hide rows 11 through 62, unless for example row 11 has data other than a 0$, then I don't want the row to collapse.

This code is the best I have worked up so far:

Sub Worksheet_Hide()

Dim lastRow As Long, myRange As Range

Application.Calculation = xlCalculationManual

lastRow = Cells(Rows.Count, 2).End(xlUp).Row

Rows.Hidden = False

For Each myRange In Range("r11:r62")

If myRange.Value = 0 Then myRange.EntireRow.Hidden = True

Next myRange

End Sub

However, for some odd reason it hides row like 15-23 for no reason. I don't get it.

Thanks.

Tlafferty,

I thought about this already. Here is the issue. The T-column, is a summation of the row, hence TTM (Twelve Month Trailing). However, what if I had an equal positive value in one cell and an equal negative value in the next. So for example, January I have $1000 expense, and in February I have a $ 1000 income. Well the T-column will sum that up and show a $ 0, when in fact that is not true. Because there is data for previous periods, it needs to be analyzed. so that's the issue I am having with analyzing just the T column, and not all of the previous columns. Otherwise it would be a simple fix. Thanks.

Originally Posted by tlafferty

OK - this "should" examine each cell one at a time to determine whether or not it contains a zero. If it does, a variable will be incremented. At the end of the data row, a check happens to see if the total number of zeros is 12 for that row. If so, the row is hidden, the variable is reset to 0 for the next row. At the end of the run, it reports in a msgbox which rows were hidden.

Sub Worksheet_Hide()
    Dim lastRow As Long
    Application.Calculation = xlCalculationManual
    lastRow = Range("G65536").End(xlUp).Row
    Rows.Hidden = False
    For Each c In Range("G11:G" & lastRow)
        For Test = 0 To 11
            If c.Offset(0, Test).Value = 0 Then
                ValTest = ValTest + 1
                If ValTest = 12 Then
                    ValTest = 0
                    c.EntireRow.Hidden = True
                    HiddenRow = HiddenRow & c.Row & ", "
                End If
            End If
        Next
    Next
    MsgBox "Hidden rows: " & HiddenRow
End Sub

Works amazingly. Thank you.