Is there a quicker way to hide blank rows

[nehpets12]

I am using the folowing code to hide blank rows
Because it deletes 1 row at a time it takes a long time

Can the code be amended to select rows as a range and hide rows opperation once only

Sub Hide_Rows()

For i = 9 To 34
If Worksheets("Purchasing Information").Cells(i, 2).Text = "" Then
Worksheets("Purchasing Information").Rows(i).Hidden = True
End If
Next i
End Sub

• Edited by Nate: Html blew up the thread. Please post a smaller sample, and make sure your Html has closing tags. Thanks.

 

[SteveO59L]

Why not just use an auto filter ?

 

[erik.van.geit]

Hi,

try something like

Range("B9:B34").SpecialCells(xlBlanks).EntireRow.Hidden = True

this will not work if there are formulas in those cells
then try

Sub test()
Dim rng As Range

    With Range("B8:B34")
    .AutoFilter Field:=1, Criteria1:="="
    Set rng = .SpecialCells(xlCellTypeVisible).EntireRow
    .AutoFilter
    End With
rng.Hidden = True
End Sub

kind regards,
Erik