macro to hide empty rows
Hmm! I see I put my "ignore this code" message on the wrong sub-thread. This
was the code you were supposed to ignore because it didn't work; HOWEVER, do
try my later posted code as that does work.
--
Rick (MVP - Excel)
"Rick Rothstein" wrote in message
...
Give this macro a try (without the dummy column) and see if it does what
you want...
Sub HideEmptyRows()
Dim R As Range
For Each R In Range("A5:BU1111").Rows
If WorksheetFunction.CountA(R) = R.Count Then R.Hidden = True
Next
End Sub
--
Rick (MVP - Excel)
"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?
|