Can you create a macro to hide rows with zero values?

N

natrujillo12

I am trying to automate emailing of financial reports to managers in our
Organization. I have one spreadsheet template with every account listed.
However, not every manager has amount in every line, and the report is pretty
long with every account listed.

I would like to create a macro to look at the a range of values in each row
of the report, and then hide any rows which have a zero value in each cell in
the range.
 
R

Ron de Bruin

Hi natrujillo12

You can use this to hide all the cells with a 0 in column A in the first 100 rows of the active sheet.

Sub UnionExample()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = StartRow To EndRow Step 1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell
ElseIf .Cells(Lrow, "A").Value = "0" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "A")
Else
Set rng = Application.Union(rng, .Cells(Lrow, "A"))
End If
End If
Next
End With
'hide all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Hidden = True

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top