Okay, I am pretty sure this macro does what you want...

Sub HideEmptyRows()
Dim R As Range
Dim SearchRange As Range
Dim LastRow As Long
Dim ColCellCount As Long
Set SearchRange = Range("A5:BU1111")
ColCellCount = SearchRange.Columns.Count
LastRow = SearchRange.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row
For Each R In SearchRange.Resize(LastRow - SearchRange(1).Row).Rows
If WorksheetFunction.CountBlank(R) = ColCellCount 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?