VBA Hiding rows

[smithani]

Hey does anyone know how I can make the following code more efficient, it seems to take a really long time to do this, the code hides the row if there is no data in the coloumn, it does it for 3 different sections:

LastRow = Range("B65000").End(xlUp).Row
For I = LastRow To 47 Step -1
If Cells(I, "I") = "" Then
Rows(I).Hidden = True
End If
Next I

LastRow = Range("B65000").End(xlUp).Row
For I = LastRow To 815 Step -1
If WorksheetFunction.IsText(Cells(I, "I")) Then GoTo nexti
If Abs(Cells(I, "I").Value) < 1 And Abs(Cells(I, "L").Value) < 1 Then
Rows(I).Hidden = True
nexti:
End If
Next I

LastRow = Range("A65000").End(xlUp).Row
For I = LastRow To 1387 Step -1
If Cells(I, "A") = "" Then
Rows(I).Hidden = True
End If
Next I

[VBA Noob]

Try adding at the start of the code

Application.ScreenUpdating = False

and then

Application.ScreenUpdating = true

at the end

VBA Noob

[smithani]

thanks, I tried that...but its still terribly slow...excel keeps freezing up

[VBA Noob]

Maybe

Dim LastRow As Long, i As Long

Application.ScreenUpdating = False

LastRow = Range("B65000").End(xlUp).Row
For i = LastRow To 47 Step -1
    Select Case i
        Case 47 To 814
        Rows(i).Hidden = IIf(Cells(i, "I") = "", True, False)
        Case 815 To 1386
    If WorksheetFunction.IsText(Cells(i, "I")) Then GoTo nexti
    If Abs(Cells(i, "I").Value) < 1 And Abs(Cells(i, "L").Value) < 1 Then
    Rows(i).Hidden = True
nexti:
    End If

        Case Is > 1386
        Rows(i).Hidden = IIf(Cells(i, "A") = "", True, False)
    End Select
Next i
Application.ScreenUpdating = True

[royUK]

It would probably be much faster to use AutoFilter, does your data have header rows?. This will be faster for empty cells.

Dim LastRow As Long
    Dim rng    As Range
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    On Error Resume Next
    Set rng = Range(Cells(1, 1), Cells(LastRow, 1)).SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    rng.EntireRow.Hidden = True