VBA Advanced Filter Criteria (How to insert UsedRange in Range Sta

[Guest]

Using XL 2003 & 97

My Code is:

Sheets("Filtered Data").Range("A1:Q5217").AdvancedFilter
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Criteria Sheet").Range(BEM_CUY), _
CopyToRange:=Range( "A1"), Unique:=False

How do I get the Range statement to sense the used range?

Otherwise Q5127 is a constant.

TIA

Dennis

 

[Bob Phillips]

How about this (not tried I am afraid)

Set oRng = Sheets("Filtered Data").UsedRange
oRng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Criteria Sheet").Range(BEM_CUY), _
CopyToRange:=Range( "A1"), Unique:=False

 

[Guest]

Bingo! It works fine.

Thanks for your Knowledge & Time

Dennis