First, your selection can in some circumstances extend to the end of the
sheet (such as ALL cells below the specified cell are blank). I would use
this to avoid that problem:
Set rng = Range(Cells(6, 7), Columns(7).SpecialCells (xlCellTypeLastCell))
Then, (minor point) no need to select r; just use row = r.Row and
r.EntireRow.Hidden. Also, since you use r repeatedly, using With r... End
With will be faster.

Those are the obvious things I can see that might help. The first one is
the main one: if your code had to loop through over 65000 rows and check and
hide them all, well, I bet it was indeed pretty slow!

"Fid" wrote:

I have a loop hiding blank rows. It just seems to run very slowly.
What can I do to increase the speed of this code?


Private Sub Worksheet_Activate()
Dim rng As Range
Set rng = Range(Cells(6, 7), Cells(6, 7).End(xlDown))
Hide_Rows rng

End Sub

Public Sub Hide_Rows(rng)

Application.ScreenUpdating = False

Dim r As Range
Dim row As Long

rng.Select
For Each r In Selection
r.Select
row = ActiveCell.row
Application.StatusBar = "Row " & row
If IsEmpty(r) Or r = "None" Then
Selection.EntireRow.Hidden = True
Else
Selection.EntireRow.Hidden = False
End If
Next r
Application.StatusBar = False
Application.ScreenUpdating = True

End Sub

Thanks,

Brent