Hiding rows that contain formulas

[Sarah Stitt]

Hi

I have checked the forum already and the solutions there re my reques
have been very helpful but do not fully solve my problem.

I have a spreadsheet that has the following:

Cell B4 - formula returns blank
Cell A5 - formula returns text
Cell F9 - formula returns blank
Cell C13 - formula returns blank
Cell D15 - formula returns number
Cell E19 - formula returns number

I want to be able to hide the rows that return blank. As my formula
are not in the same column none of the solutions seem to work.

Any help would be apprciated. Many thanks

Sara

 

[Ron de Bruin]

Try this one Sarah

It will check the whole row for formulas that evaluates to ""

Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

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

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

On Error Resume Next
If .Rows(Lrow).Cells.SpecialCells(xlCellTypeFormulas).Count <> 0 Then
If Err.Number > 0 Then
On Error GoTo 0
Else
If Application.WorksheetFunction.CountIf(.Rows(Lrow) _
.Cells.SpecialCells(xlCellTypeFormulas), "") > 0 Then .Rows(Lrow).Hidden = True
End If
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

 

[Sarah Stitt]

Ron

Thanks very much - this works wonderfully although just one mor
question

Cell B4 - formula returns blank
Cell D4 - formula returns a number

is there a solution that will not hide this row as it does have
result in cell D4.

Thanks

Sara

 

[Ron de Bruin]

Untested

Try this Sarah


Sub Example2()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

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

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

On Error Resume Next
If .Rows(Lrow).Cells.SpecialCells(xlCellTypeFormulas).Count <> 0 Then
If Err.Number > 0 Then
On Error GoTo 0
Else
If Application.WorksheetFunction.CountIf(.Rows(Lrow) _
.Cells.SpecialCells(xlCellTypeFormulas), "") = _
.Rows(Lrow).Cells.SpecialCells(xlCellTypeFormulas).Count Then _
.Rows(Lrow).Hidden = True
End If
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

 

[Sarah Stitt]

Hi Ron

Sorry but this does not work - Row 4 is still being hid even though th
formula produces a number.

Any more ideas would be appreciated.

Many thanks

Sara

 

[Ron de Bruin]

Hi Sarah

I have tested it also now and it is working for me?
Can anybody else test my macro please

 

[Sarah Stitt]

Hi Ron

I have attached the file and the cells highlighted in blue hav
formulas in them.

As I said earlier both your macros worked wonderfully but I have copie
and pasted so many times that I can't get either to work the way
want.

I really appreciate this help as I have been struggling for some tim
now.

Many thanks

Sara

Attachment filename: hiding rows4.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=51969

 

[Ron de Bruin]

Hi Sarah

Don't post files in the newsgroup
Send it to me private and I look at it for you

 

[Ron de Bruin]

Hi

You are right my example is not working
I try to send you a example this evening

 

[Ron de Bruin]

Ok

Try this one

Sub Example3()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim rng As Range
Dim cell As Range
Dim num As Long

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

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

On Error Resume Next
Set rng = .Rows(Lrow).Cells.SpecialCells(xlCellTypeFormulas)
If Err.Number > 0 Then
On Error GoTo 0
Else
num = 0
For Each cell In rng
If cell = "" Then num = num + 1
Next cell
If num = rng.Cells.Count Then .Rows(Lrow).Hidden = True
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


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

Hi

You are right my example is not working
I try to send you a example this evening

 

[Sarah Stitt]

Hi Ron

Many, many thanks for all your help in such a short time. This work
wonderfully.

Regards

Sara

 

[Ron de Bruin]

You are welcome

The countif is not working with specialcells.
That's why the first example not run correct

Stupid from me