Hiding rows
 

I have the following formula in cell A5 and throughout the sheet
=if(Sum(A1:A4)0,Sum(A1:A4),"") If the answer returns "" (blank) I want my
macro to hide the row using
"Range("MyRange").Columns(5).SpecialCells(xlBlanks ).EntireRow.Hidden = True".
However the code sees the formula and says it is not blank. How can I get
around that?

Hiding rows
 

Hi pkeegs

You must loop through the range
Try this example

My EasyFilter add-in have also this option
http://www.rondebruin.nl/easyfilter.htm

Sub Example3()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long

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

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 5
EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For Lrow = EndRow To StartRow 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 = "" Then .Rows(Lrow).Hidden = True

End If
Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

--
Regards Ron De Bruin
http://www.rondebruin.nl



"pkeegs" wrote in message ...
I have the following formula in cell A5 and throughout the sheet
=if(Sum(A1:A4)0,Sum(A1:A4),"") If the answer returns "" (blank) I want my
macro to hide the row using
"Range("MyRange").Columns(5).SpecialCells(xlBlanks ).EntireRow.Hidden = True".
However the code sees the formula and says it is not blank. How can I get
around that?

Hiding rows
 

Thanks Ron,
I had not thought about a loop and I have ended up using one. I don't have
sufficient knowledge of VBA to understand your language, but simply looping
through the cells with an if statement is giving me the result I require.

"Ron de Bruin" wrote:

Hi pkeegs

You must loop through the range
Try this example

My EasyFilter add-in have also this option
http://www.rondebruin.nl/easyfilter.htm

Sub Example3()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long

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

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 5
EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For Lrow = EndRow To StartRow 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 = "" Then .Rows(Lrow).Hidden = True

End If
Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

--
Regards Ron De Bruin
http://www.rondebruin.nl



"pkeegs" wrote in message ...
I have the following formula in cell A5 and throughout the sheet
=if(Sum(A1:A4)0,Sum(A1:A4),"") If the answer returns "" (blank) I want my
macro to hide the row using
"Range("MyRange").Columns(5).SpecialCells(xlBlanks ).EntireRow.Hidden = True".
However the code sees the formula and says it is not blank. How can I get
around that?