Harold,

I've included the two separate ways below that you mention. In general,
performing methods on an aggregate basis rather than a one-by-one basis is
fastest, thus, you'll see the Union function below. In your situation, when
you delete one-by-one, Excel may be recalculating after every deletion, so
you may have up to 90 different recalculations on a one-by-one basis rather
than 1 recalculation on the aggregate. You can loop one-by-one if you so
desire because a loop of 90 items is practically negligible. (Also, I would
comment the .EntireRow.Delete syntax below and uncomment the Debug.Print
lines. Debug.Print will print to the Immediate Window (View | Immediate
Window) and will allow you to see how the program is behaving prior to
executing a deletion).

Best,

Matthew Herbert

Sub TestDeleteBlanks()
Dim rngEval As Range
Dim rngDelete As Range
Dim rngCell As Range

Set rngEval = ActiveSheet.Range("F9:F98")
Set rngDelete = rngEval.SpecialCells(xlCellTypeBlanks)
'Debug.Print rngDelete.Address
rngDelete.EntireRow.Delete

Set rngDelete = Nothing

'-OR-

For Each rngCell In rngEval.Cells
If rngCell.Value = "" Then
If rngDelete Is Nothing Then
Set rngDelete = rngCell
Else
Set rngDelete = Union(rngDelete, rngCell)
End If
End If
Next rngCell

'Debug.Print rngDelete.Address
rngDelete.EntireRow.Delete

End Sub

"Harold Good" wrote:

Hi,

I'd like to hide unused rows in a budget form based on a formula in Col F of
any row in the range below that is equal to "". I know little about VBA,
but the code below works, thhough it is too slow to do everytime it
recalculates. Because Column F values are entered from a separate worksheet,
I cannot use the Worksheet_Change Event.

Private Sub Worksheet_Calculate()
Dim cell As Range
Application.EnableEvents = False
For Each cell In Range("F9:F98")
If cell.Value = "" Then
cell.EntireRow.Hidden = True
End If
Next cell
Application.EnableEvents = True
End Sub

What I've read elsewhere is to avoid Loops whenever possible. Since all the
rows that do not equal "" are at the top, and all those that equal "" are at
the bottom, is there a better way to do this using CountA, or SpecialCells?

Thanks for any help you can offer,
Harold