Advanced Filter Macro

[Dolphinv4]

Hi,

i tried to write a macro for advanced filter as below but it doesn't work.
Please help.

The main data are in the "Raw" worksheet & the criterias is in the "Bank
List" worksheet.

With Worksheets("Bank List")
Set myrng = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

Worksheets("Raw").Cells.AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Sheets("Bank List").Range(myrng), Unique:=False

Thanks.

Dolphin

 

[Joel]

I think you have the source and destination ranges backwards. This code works

With Worksheets("Bank List")
Set myrng = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

myrng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Worksheets("Raw").Range("A1"), Unique:=False

 

[Dave Peterson]

myrng is already a range.

That means that excel/vba knows all about it--it knows that it's on the Bank
List worksheet. So you can't specify that with "sheets("bank
list").range(myrng).

And you can't wrap myrng with range(), either.

Worksheets("Raw").Cells.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=myrng, Unique:=False


But I'd be more specific. I'd use the same kind of code to tell what column to
filter on the Raw worksheet (I changed some variable names, too):

Dim myCriteriaRng As Range
Dim myRngToFilter As Range

With Worksheets("Bank List")
Set myCriteriaRng = .Range("A1:A" _
& .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With Worksheets("raw")
Set myRngToFilter = .Range("a1:A" _
& .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

myRngToFilter.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=myCriteriaRng, Unique:=False