Variable as argument in filter range

[Bert]

I'm trying to use the following to do filtering of a list of changing
length.
x = LastRow() ' this is a function that returns the last row in the
current list
Rng = "$A$2:$C$" & Trim(Str(x)) ' this concatenates that number to a
variable to
Range("A2").Select ' this sets the selection at the top of the list.

' from what I can determine the following to lines should be identical. BUT
the one with the variable causes an error.
' while the second line works fine.

Range(Rng).AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range("I2:I3"), CopyToRange:=Range("M1:O1"), Unique:=False

Range("A1:C119").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range("I2:I3"), CopyToRange:=Range("M1:O1"), Unique:=False

I've tried creating Rng to equal "$A$2:$C$119" and tried
Range(Cells(2,1),Cells(x,3)) all with the same error: "Run-time error 1004.
The extract range has a missing or illegal field name."

Any suggestions would be appreciated.
Thanks
Bert

 

[Tom Ogilvy]

if
Dim rng as Range
set rng = Range("A1:C119")
or
set rng = Range("A1"C" & x)
then

Range(rng) is illegal syntax

so
Range(Rng).AdvancedFilter

should be
Rng.AdvancedFilter

 

[Bert]

I'm still getting the error message.
I know I've gotten the rng defined correctly (thanks) because it works
correctly with rng.Select
but
rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("I2:I3"),
CopyToRange:=Range("M1:O1"), Unique:=False
is still not working. ("Run-time error 1004. The extract range has a
missing or illegal field name.")


Bert

 

[Tom Ogilvy]

The extract range has a

missing or illegal field name.")

so Range("M1:O1") contains something other than a column name from the data
source.

If that doesn't appear clear or to agree with what is there, then for your
initial test, change Range("M1:O1") to Range("M1") and make sure that cell
and the ones to the right are blank. That should get it working. Then you
can try getting more sophisticated.