Macro to hide rows based on column value

A couple options

If you have no negative numbers in your range then this will work, assuming your range is A2:B800, with A1:B1 as headers:

Sub HideRows()
On Error Resume Next
With Range("A2:B800")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
End Sub

If you mean by saying that cells containing 0 really are cells containing nothing, then you can substitute the line:

If WorksheetFunction.Sum(.Rows(i)) = 0 Then

With:

If WorksheetFunction.CountBlank(.Rows(i)) = 2 Then

Hope this helps.

Tom Urtis

Re: A couple options

This certainly gives me some direction, except the columns do contain values in the negative as well. In one case I want to hide the row only if both the cell in column A AND the cell in column D=0.

Another one I have is to hide the row if column A=0 and column D is empty.

Anything you caan give me would be great. Thanks so much!!! :-)

Re: A couple options

See if this gets you any closer. I can't tell whether you want to only include column A and column B (per your original post), or if your original reference to Column B is a typo because you refer to Columns A and D in your second post. This new code goes A thru D, with help from Barrie Davidson's post a few weeks ago for a similar question.

Sub HideRows()
With Range("A2:D800")
.EntireRow.Hidden = False
For Each cell In Range("A2:A" & ActiveCell.SpecialCells(xlCellTypeLastCell).Row)
Select Case cell.Value
Case Is = 0
cell.EntireRow.Hidden = True
Case Is = ""
cell.EntireRow.Hidden = True
End Select
Next cell
End With
End Sub

Tom U.

Re: A couple options

Would the following work so that it looks at one column for a zero and to another for a blank. The two columns are not next to one another and I want to ignore the range in between. Here is the EXACT scenario:

If the value in column Q = 0 AND the value in column D is "" (blank) then hide the entire row.

Would the following work for that. I tried it but get a compile error: variable not found on the work cell, as in "For each cell" - Any idea why this is happening?

Sub Budget2002_HideRows()
With Range("D12:Q800")
.EntireRow.Hidden = False
For Each cell In Range("Q12:Q" & ActiveCell.SpecialCells(xlCellTypeLastCell).Row)
Select Case cell.Value
Case Is = 0
cell.EntireRow.Hidden = True
For Each cell In Range("D12:D" & ActiveCell.SpecialCells(xlCellTypeLastCell).Row)
Select Case cell.Value
Case Is = ""
cell.EntireRow.Hidden = True
End Select
Next cell
End With
End Sub

: This certainly gives me some direction, except the columns do contain values in the negative as well. In one case I want to hide the row only if both the cell in column A AND the cell in column D=0. : Another one I have is to hide the row if column A=0 and column D is empty. : Anything you caan give me would be great. Thanks so much!!! :-)

See a possible answer at 8581.html

If the value in column Q = 0 AND the value in column D is "" (blank) then hide the entire row. Would the following work for that. I tried it but get a compile error: variable not found on the work cell, as in "For each cell" - Any idea why this is happening? Sub Budget2002_HideRows() With Range("D12:Q800") For Each cell In Range("Q12:Q" & ActiveCell.SpecialCells(xlCellTypeLastCell).Row) For Each cell In Range("D12:D" & ActiveCell.SpecialCells(xlCellTypeLastCell).Row) Next cell End Sub

: See if this gets you any closer. I can't tell whether you want to only include column A and column B (per your original post), or if your original reference to Column B is a typo because you refer to Columns A and D in your second post. This new code goes A thru D, with help from Barrie Davidson's post a few weeks ago for a similar question. : Sub HideRows() : With Range("A2:D800") : For Each cell In Range("A2:A" & ActiveCell.SpecialCells(xlCellTypeLastCell).Row) : Select Case cell.Value : Case Is = 0 : cell.EntireRow.Hidden = True : Case Is = "" : cell.EntireRow.Hidden = True : End Select : Next cell : Tom U. :