faster way to hide rows w/o using autofilter?

[[email protected]]

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

[Bob Phillips]

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)

<[email protected]> wrote in message
news:[email protected]...
> 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
>

[Tom Ogilvy]

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.Rows
If Intersect(r, Range("BUColumn")).Value <> strBUChoice Then
if rng is nothing then
set rng = r
else
set rng = union(r,rng)
end if
Next r
if not rng is nothing then
rng.entirerow.hidden = True
end if
Application.ScreenUpdating = True
End Sub

Might be faster.

--
Regards,
Tom Ogilvy

"[email protected]" wrote:

> 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
>
>

[David McRitchie]

Hi Barbetta,
You could always turn off screen updating and calculation, even some
of the worst code could be made to look better than some of the best code.
So I'd also include turning off calculation -- in fact you generally get
a bigger boost out of that alone than turning off screen updating.
http://www.mvps.org/dmcritchie/excel/slowresp.htm

Hopefully EmployeeTable does not go down entire columns, as
cycling through every row in one colum could take 3 minutes on a
600mHz machine, even with screen updating and calculation turned off.

But if it is filtering that you really want to do, I think you'd be better
of doing the filtering in your code that way everything is builtin and
is faster. Debra Dalgleish and John McGimpsey both have examples
on their sites.
http://www.contextures.com/tiptech.html
http://www.mcgimpsey.com/excel/
Filtering has an advantage that you can copy and paste filtered rows
and that is all that will get pasted, and if you do a Mail Merge in Word
with Excel as your database -- the filtered rows are all that will get seen.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

<[email protected]> wrote in message news:[email protected]...
> 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
>