CODEWITHSUNDEEP
vbafilterrangeautofilter
Asifa.K
Asifa.K

Reputation: 35

VBA filter from a range that have been set

I am trying to use Autofilter command. I want the range to be a table that I have set as following

Dim myTable As Range
Set myTable = ActiveSheet.Range("A1").CurrentRegion

So I thought when I want to filter, I should be doing something like

ActiveSheet.ListObjects("myTable").AutoFilter Field:=10, Criteria1:="AUGUST"

But there seems to be an error saying "Subscript out of range"

Maybe I am wrong about the range part. Because when I use eg:range("A1:K500"), it works fine. But I really prefer it to be in the range of CurrentRegion since this data might be in different range every month, so it would be too much work to change the range in VBA manually everytime. Thanks

Upvotes: 1

Views: 350

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Try replacing

ActiveSheet.ListObjects("myTable").AutoFilter Field:=10, Criteria1:="AUGUST"

with

myTable.AutoFilter Field:=10, Criteria1:="AUGUST"

If there is a table named "myTable" you can use your above code, not needing of any preliminary range set. If there is only one table, you can use:

ActiveSheet.ListObjects(1).AutoFilter Field:=10, Criteria1:="AUGUST"

Upvotes: 1

Related Questions