Having problems clearing a row.
 

Dear folks
I found some code from this site, regarding clearing out empty spaces and hiding the row. However, if the row contains formulas, the row is not hidden. Is there a better code some one might know that tackles this issue or make a modification to this code, the code I used is below

Sub mergeall(

Dim r As Lon
Application.ScreenUpdating = Fals
With Worksheets("Sheet2"
For r = .UsedRange.Rows(.UsedRange.Rows.Count).Row To 128 Step -
If Application.CountA(Range(Cells(r, "D"), Cells(r, "F"))) = 0 The
.Rows(r).Hidden = Tru
End I
Nex
End Wit


Application.ScreenUpdating = Tru
End Su

Any pointers will be appreciated

Cheers

Ranjan

Having problems clearing a row.
 

Ranjan,

If you have formulas that return "" values, then you could modify your code
by changing

If Application.CountA(Range(Cells(r, "D"), Cells(r, "F"))) = 0 Then

to

If (Cells(r, "D").Value = "") And (Cells(r,"E").Value = "") And (Cells(r,
"F").Value) = "" Then

HTH,
Bernie
MS Excel MVP


"Ranjan as usual" wrote in message
...
Dear folks,
I found some code from this site, regarding clearing out empty spaces and
hiding the row. However, if the row contains formulas, the row is not
hidden. Is there a better code some one might know that tackles this issue
or make a modification to this code, the code I used is below.

Sub mergeall()


Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet2")
For r = .UsedRange.Rows(.UsedRange.Rows.Count).Row To 128 Step -1
If Application.CountA(Range(Cells(r, "D"), Cells(r, "F"))) = 0
Then
.Rows(r).Hidden = True
End If
Next
End With


Application.ScreenUpdating = True
End Sub

Any pointers will be appreciated.

Cheers,

Ranjan

Having problems clearing a row.
 

Hey Bernie
It worked great. Thanx for the help
Cheers

Ranjan