CODEWITHSUNDEEP
excelvbaautofilter
D. Ace
D. Ace

Reputation: 80

Is it possible to specify dynamic range in auto filter syntax?

I have written below code to apply auto filter. Code is working fine. However I have specified static range, here: *Range("A1", **"M"** & lastRow).AutoFilter* . Is there a way to replace "M" with last existing column number in sheet2. I have calculated last existing column *myCol = rngFound.Column - 1*. But not sure how to use it. Please help !!!

My Code:

Sub testfilter1()
Dim lastRow As Long
Dim myCol As Long
Dim rngFound As Range

ThisWorkbook.Sheets("sheet2").Activate

 lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

        Set rngFound = ActiveSheet.Rows(1).Find(What:="", LookIn:=xlValues, LookAt:=xlWhole, _
            SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

            myCol = rngFound.Column - 1 ' this will give last used column

Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("A1", "M" & lastRow).AutoFilter Field:=4, Criteria1:="*somename*"

End Sub

Upvotes: 1

Views: 78

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

You could use Cells() within Range():

...Range(Cells(1,1),Cells(lastRow,myCol))...

Cells(1,1) is the cell A1. The format is Cells([row],[column])

Edit: Just realized you could also use Range("A1",Cells(lastRow,myCol)). Personally, if I use Cells() I do it in both places in Range(), but that's personal preference and this other way should work for you too.

Upvotes: 4

Related Questions