Variable as argument in filter range

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

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

--
Regards,
Tom Ogilvy

"Bert" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "Bert" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

>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.

--
Regards,
Tom Ogilvy




"Bert" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Bert" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>