Slow VBA code....Hide/Unhide Loop

[Tami]

I have the following code to hide and display rows as needed. For example if
cell k1=000 display all rows. if k1=846, then any row with 846 in column in
I will be displayed.
It works well but its really slow.
Does anyone see what may be slowing it down?
Thanks in advance for any and all advice.
t.




Dim class As Variant, i As Long

If Intersect(Target, Range("k1")) Is Nothing Then Exit Sub
Me.unprotect Password:="paspas"
Application.ScreenUpdating = False
Application.EnableEvents = False

UsedRange.Rows.Hidden = False

If Cells(1, 11) = "000" Then
Columns("B").Select

If Columns.Hidden = False Then
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveWindow.FreezePanes = False
Rows("2:3").Hidden = True
Range("k8").Select
ActiveWindow.FreezePanes = True

Else

ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveWindow.FreezePanes = False
Rows("2:3").Hidden = True
Range("p8").Select
ActiveWindow.FreezePanes = True

End If

Else

class = Cells(1, 11).Value

For i = 8 To Cells(400, 9).End(xlUp).Row

If Cells(i, 9) <> class Then Rows(i).Hidden = True

Next i

Columns("B").Select

If Columns.Hidden = False Then

ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveWindow.FreezePanes = False
Range("k6").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
ActiveWindow.FreezePanes = True

Else


ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveWindow.FreezePanes = False
ActiveCell.Select

Rows("2:3").Hidden = True



Range("p6").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
ActiveWindow.FreezePanes = True

End If

End If

Application.ScreenUpdating = True
Application.EnableEvents = True


Me.Protect Password:="paspas", AllowInsertingRows:=False,
AllowDeletingRows:=False



End Sub

 

[Shane Devenshire]

Although I don't follow all of your code, it looks like you are looping
through a range to hide rows, it would be far faster to use Data, Filter,
AutoFilter (or Advanced Filter). You can record that command.

 

[Tami]

ok, i've tried autofilter and can't get that to work properly...this is not a
straight forward dump with one cell header names...i've got merged cells
etc...but i wanted to know if i could speed up in another way...

for example, i know loops are slow...i've read so much about this and you
confirmed this...but my loop uses "For i=8 to 400" and a Next...Is that kind
of loop the same slowness as the below example: which uses a Do-Until-Loop
approach?

Sub HideRow()

Range("B15").Select

Do Until ActiveCell = "STOP"
If ActiveCell.Value = 0 Then
ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End if
Loop

End Sub

*** OR possibly another scenrio to cut the loop time: my current loop goes
from i=8 to 400....could i change the 400 to refere the last cell of the
worksheet which could be as low as 100 (the last cell in the searched column
is named "end_dept") so could i loop throught that row number? not sure how
to get the row number of "end_dept" in the loop.

again, thx in advance for any suggestsions.
tami