Macro to hide rows based on criteria

[Santa-D]

I am in the process of doing end of year financial reporting analysis
and I want to be able to run a macro that hides empty rows based on a
specific criteria.

Column C lists the accounts codes, column G is the YTD Actuals and
column H is the YTD Budget.

What I want to do, is.....

If there is a value in a row in Column C and if the value of the cell
in both column G & H is 0 the hide the row.

If anyone can point me in the right direction to research this further
would be sincerely appreciated.

PS: I am using MS Excel 2003

 

[Jarek Kujawa]

try to select yr data and use the macro:

Sub hideeeee()
On Error Resume Next
For Each cell In Selection
If IsNumeric(cell) And cell.Offset(0, 4) = 0 And cell.Offset(0, 5) = 0
Then
cell.Rows.EntireRow.Hidden = True
End If
Next cell

End Sub

HIH

 

[Mike H]

Try this

Sub stantial()
Dim myrange, hiderange As Range
Lastrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set myrange = Range("C1:C" & Lastrow)
For Each c In myrange

If c.Value <> "" And c.Offset(, 4) = 0 And c.Offset(, 5) = 0 _
And c.Offset(, 4) <> "" And c.Offset(, 5) <> "" Then
If hiderange Is Nothing Then
Set hiderange = c.EntireRow
Else
Set hiderange = Union(hiderange, c.EntireRow)
End If
End If
Next
If Not hiderange Is Nothing Then
hiderange.EntireRow.Hidden = True
End If
End Sub

Mike

 

[Jarek Kujawa]

Mike is right
an empty cell will been as '0', so I have to adjust my macro

Sub hideeeee()
On Error Resume Next
For Each cell In Selection
If IsNumeric(cell) And cell.Offset(0, 4) = 0 And cell.Offset(0, 5) = 0
_
And Len(cell.Offset(0, 4)) > 0 And Len(cell.Offset(0, 5) > 0 Then
cell.Rows.EntireRow.Hidden = True
End If
Next cell

End Sub

sorry

 

[Santa-D]

Thanks Jarek & Mike,

I was on the verge of using the xlUp but I've got a series of blank
rows in between each of the categories.
I ended up using a modified version of the with selection...

"E" for expense & "R" for revenue. If the value was "" then it would
skip and if it was "H" for Heading then it would be fine.


Sub hideeeee()
On Error Resume Next
For Each cell In Selection
If cell.Offset(0, -2) = "R" Or cell.Offset(0, -2) = "E" Then
If cell.Offset(0, 4) = 0 And cell.Offset(0, 5) = 0 _
And Len(cell.Offset(0, 4)) > 0 And Len(cell.Offset(0, 5)) > 0
Then
cell.Rows.EntireRow.Hidden = True
End If
End If
Next cell


End Sub

 

[Santa-D]

Thanks Jarek & Mike,

I was on the verge of using the xlUp but I've got a series of blank
rows in between each of the categories.
I ended up using a modified version of the with selection...

"E" for expense & "R" for revenue.  If the value was "" then it would
skip and if it was "H" for Heading then it would be fine.

Sub hideeeee()
On Error Resume Next
For Each cell In Selection
If cell.Offset(0, -2) = "R" Or cell.Offset(0, -2) = "E" Then
    If cell.Offset(0, 4) = 0 And cell.Offset(0, 5) = 0 _
      And Len(cell.Offset(0, 4)) > 0 And Len(cell.Offset(0, 5)) > 0
Then
cell.Rows.EntireRow.Hidden = True
End If
End If
Next cell

End Sub

The only downside is that it didn't keep the highlight second row
conditional formatting when hiding all those rows.......