hiding rows containing formulas which return zero values
 

I use code to hide empty rows on a sheet. This works fine if the cells dont
have formulas in them and are empty.

However, if I use formulas in those rows which return blank cells, the code
doesnt hide the rows.

Even though the formulas in the cells return a zero value based upon
criteria, the rows wont hide.

Here is the code i use:

Dim rng As Range
On Error Resume Next
Range("a28:a54").EntireRow.Hidden = False
Set rng = Range("a28:a54").SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.Hidden = True
End If


Can anyone suggest code which will hide the rows if the formulas in cells
a28 to a 54 return zero values?

Thanks,

Roger

hiding rows containing formulas which return zero values
 

If you use formulas in the cells, then they do not meet the xlBlank criteria
and will not hide, not matter that the value is zero. The cell is not blank,
it has a formula.
You will probably have to use a For Each statement and check for "" Or
..Value = 0.
Dim c As Range
For Each c In Range"A28:A54")
If c = "" Or c.Value = 0 Then
c.EntireRow.Hidden = True
End If
Next

"Roger on Excel" wrote:

I use code to hide empty rows on a sheet. This works fine if the cells dont
have formulas in them and are empty.

However, if I use formulas in those rows which return blank cells, the code
doesnt hide the rows.

Even though the formulas in the cells return a zero value based upon
criteria, the rows wont hide.

Here is the code i use:

Dim rng As Range
On Error Resume Next
Range("a28:a54").EntireRow.Hidden = False
Set rng = Range("a28:a54").SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.Hidden = True
End If


Can anyone suggest code which will hide the rows if the formulas in cells
a28 to a 54 return zero values?

Thanks,

Roger

hiding rows containing formulas which return zero values
 

Sub tst()

Range("a28").Select
For i = 1 To 50
If ActiveCell.Value = 0 Then
ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Activate
ElseIf ActiveCell.Value = "" Then
ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
Next i

End Sub


This is not pretty, but it gets the job done.

dmoney

"Roger on Excel" wrote:

I use code to hide empty rows on a sheet. This works fine if the cells dont
have formulas in them and are empty.

However, if I use formulas in those rows which return blank cells, the code
doesnt hide the rows.

Even though the formulas in the cells return a zero value based upon
criteria, the rows wont hide.

Here is the code i use:

Dim rng As Range
On Error Resume Next
Range("a28:a54").EntireRow.Hidden = False
Set rng = Range("a28:a54").SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.Hidden = True
End If


Can anyone suggest code which will hide the rows if the formulas in cells
a28 to a 54 return zero values?

Thanks,

Roger

hiding rows containing formulas which return zero values
 

Had a typo:

Dim c As Range
For Each c In Range("A28:A54")
If c = "" Or c.Value = 0 Then
c.EntireRow.Hidden = True
End If
Next


"Roger on Excel" wrote:

I use code to hide empty rows on a sheet. This works fine if the cells dont
have formulas in them and are empty.

However, if I use formulas in those rows which return blank cells, the code
doesnt hide the rows.

Even though the formulas in the cells return a zero value based upon
criteria, the rows wont hide.

Here is the code i use:

Dim rng As Range
On Error Resume Next
Range("a28:a54").EntireRow.Hidden = False
Set rng = Range("a28:a54").SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.Hidden = True
End If


Can anyone suggest code which will hide the rows if the formulas in cells
a28 to a 54 return zero values?

Thanks,

Roger