CODEWITHSUNDEEP
excelvba
Alegro
Alegro

Reputation: 7956

How to faster filter a range?

I want to filter a range (9 columns and 1400 rows) by values in a column (rng01)

Application.ScreenUpdating = False
db.Rows.Hidden = False
For Each cell In rng01.Cells
If Not cell.Value = "323" Then
cell.EntireRow.Hidden = True
End If
Next cell
Application.ScreenUpdating = True

This works, but takes about 3-4 seconds.
Is there a faster way, pls ?

Upvotes: 1

Views: 274

Answers (1)

chris neilsen
chris neilsen

Reputation: 53137

Lets see if I understand your question: rng01 is one of the nine columns, you want to filter so that only rows where the value in rng01 = 323 are visible.

You can use AutoFilter for this. You will need a header row above your data (but it can be blank). 

rng01.AutoFilter
rng01.AutoFilter Field:=1, Criteria1:="323", VisibleDropDown:=False

If the sheet rng01 referes to already has an active AutoFilter, the first rng01.AutoFilter clears it.

Note that rng01 is one column wide.

If in fact rng01 referes to a range wider than one column, use

rng01.Columns(3).AutoFilter
rng01.Columns(3).AutoFilter Field:=1, Criteria1:="323", VisibleDropDown:=False

Upvotes: 2

Related Questions