If your "ROW_SHRINK_AREA" and Range("A5:BU1111") have no intersections,
then try this one.
Sub testgoawayEmpty()
Dim i As Long
Application.ScreenUpdating = False
For i = 5 To 1111
If Application.CountA(Range(Cells(i, "A"), Cells(i, "BU"))) = 0 Then
Rows(i).Hidden = True
End If
Application.StatusBar = "Now is in Rows(" & i & " )"
Next
End Sub
Keiji
marjattanb wrote:
Thanks - but I still seem to be stuck. I made it to look like this:
Sub hideemptyrows()
Application.Goto Reference:="ROW_SHRINK_AREA"
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Application.CountA(Rows(i)) = 0 Then Rows(i).Hidden = True
Next i
End Sub
- thinking that it would look for zeroes within my dummy column and
consequently hide those rows entirely. Still nothing happens - in addition of
the area being selected.
Anything I do wrong? - sure there is! :)
Thanking again
Sub hideemptyrows()
Application.Goto Reference:="ROW_SHRINK_AREA"
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Application.CountA(Rows(i)) = 0 Then Rows(i).Hidden = True
Next i
End Sub
"Don Guillett" wrote:
You may desire to use a macro that looks at the number of hits
Sub hideemptyrows()
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Application.CountA(Rows(i)) < 1 Then Rows(i).Hidden = True
Next i
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"marjattanb" wrote in message
...
Hello,
I am trying to hide empty rows in a named range (A5:BU1111) where all
"empty" rows however do have a formula in several columns (where the value
is
"").
The only way I have managed is to create a dummy column (which I have
named
"ROW_SHRINK_AREA", which creates some values which indicate that the row
in
question is not empty. Then I use the macro:
Sub MakeEmptyRowsGoAway()
Application.Goto Reference:="ROW_SHRINK_COLUMN"
For Each R In Application.Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Range("ROW_SHRINK_COLUMN")).Cells
varValue = R.Value
If IsNumeric(varValue) Then
If varValue = 0 Then
R.EntireRow.Hidden = True
End If
End If
Next R
End Sub
Would someone please advise me a faster method?