See if this is quicker

Public Sub DropDownBU_Change(Optional bPrinting As Boolean)
Dim strBUChoice As String, r As Range, rngEmployees As Range
Dim rng As Range
Application.ScreenUpdating = False
Cells.Rows.Hidden = False
strBUChoice = Range("CurrentFilterSetting").Text
Set rngEmployees = Range("EmployeeTable")
For Each r In rngEmployees.Columns(Range("BUColumn").Column -
rngEmployees.Column + 1).Cells
If r.Value < strBUChoice Then
If rng Is Nothing Then
Set rng = r
Else
Set rng = Union(r, rng)
End If
End If
Next r
If Not rng Is Nothing Then rng.EntireRow.Delete
Application.ScreenUpdating = True
End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

wrote in message
ps.com...
I'm trying to hide rows without using autofilter (I need to execute
code when filter choice is changed and don't want to have to use
Worksheet_Calculate because of all the complications it can cause), so
I"m using a dropdown from the forms toolbar. My problem is the
execution is slow. Is there a faster way to do this? Here's my code:

Public Sub DropDownBU_Change(Optional bPrinting As Boolean)
Dim strBUChoice As String, r As Range, rngEmployees As Range
Application.ScreenUpdating = False
Cells.Rows.Hidden = False
strBUChoice = Range("CurrentFilterSetting").Text
Set rngEmployees = Range("EmployeeTable")
For Each r In rngEmployees.Rows
If Intersect(r, Range("BUColumn")).Value < strBUChoice Then
r.Hidden = True
Next r
Application.ScreenUpdating = True
End Sub