Advanced Filter to Dynamic Range

I've applied an Advanced Filter to a Range and simply recorded the macro,
extract below, but my problem is that my data in the Database sheet is
dynamic and changes in Row length (not by column)

How can I incorporate a dynamic range via VBA with Advanced Filters?

Thanks



Sheets("Database").Select
Range("A1:T61").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
"V1:V2"), CopyToRange:=Range("X1:AQ1"), Unique:=False
Range("A1").Select

Sheets("Database").Select
Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy,
CriteriaRange:=Range("V1:V2"), _
CopyToRange:=Range("X1:AQ1"), Unique:=False
Range("A1").Select

--
Regards,
Tom Ogilvy



"John" <r.@9999yahoo.co.uk> wrote in message
news:dl57c9$i30$1@reader01.news.esat.net...
> I've applied an Advanced Filter to a Range and simply recorded the macro,
> extract below, but my problem is that my data in the Database sheet is
> dynamic and changes in Row length (not by column)
>
> How can I incorporate a dynamic range via VBA with Advanced Filters?
>
> Thanks
>
>
>
> Sheets("Database").Select
> Range("A1:T61").AdvancedFilter Action:=xlFilterCopy,
> CriteriaRange:=Range( _
> "V1:V2"), CopyToRange:=Range("X1:AQ1"), Unique:=False
> Range("A1").Select
>
>

Dim LastRow as Long
LastRow = Sheets("Database").Cells(Rows.Count,1).End(xlup).Row


With Sheets("Database").Select
Range("A1:T" & LastRow).AdvancedFilter Action:=xlFilterCopy,

CriteriaRange:=Range( _
"V1:V2"), CopyToRange:=Range("X1:AQ1"), Unique:=False
Range("A1").Select

--
Cheers
Nigel



"John" <r.@9999yahoo.co.uk> wrote in message
news:dl57c9$i30$1@reader01.news.esat.net...
> I've applied an Advanced Filter to a Range and simply recorded the macro,
> extract below, but my problem is that my data in the Database sheet is
> dynamic and changes in Row length (not by column)
>
> How can I incorporate a dynamic range via VBA with Advanced Filters?
>
> Thanks
>
>
>
> Sheets("Database").Select
> Range("A1:T61").AdvancedFilter Action:=xlFilterCopy,
> CriteriaRange:=Range( _
> "V1:V2"), CopyToRange:=Range("X1:AQ1"), Unique:=False
> Range("A1").Select
>
>

Thanks Guys

"Nigel" <nigel-sw@suxnospampanet.com> wrote in message
news:e1uErv65FHA.2628@TK2MSFTNGP11.phx.gbl...
> Dim LastRow as Long
> LastRow = Sheets("Database").Cells(Rows.Count,1).End(xlup).Row
>
>
> With Sheets("Database").Select
> Range("A1:T" & LastRow).AdvancedFilter Action:=xlFilterCopy,
>
> CriteriaRange:=Range( _
> "V1:V2"), CopyToRange:=Range("X1:AQ1"), Unique:=False
> Range("A1").Select
>
> --
> Cheers
> Nigel
>
>
>
> "John" <r.@9999yahoo.co.uk> wrote in message
> news:dl57c9$i30$1@reader01.news.esat.net...
>> I've applied an Advanced Filter to a Range and simply recorded the macro,
>> extract below, but my problem is that my data in the Database sheet is
>> dynamic and changes in Row length (not by column)
>>
>> How can I incorporate a dynamic range via VBA with Advanced Filters?
>>
>> Thanks
>>
>>
>>
>> Sheets("Database").Select
>> Range("A1:T61").AdvancedFilter Action:=xlFilterCopy,
>> CriteriaRange:=Range( _
>> "V1:V2"), CopyToRange:=Range("X1:AQ1"), Unique:=False
>> Range("A1").Select
>>
>>
>
>

Guys I'm getting a syntax error on both your codes, am I missing something?
Where does the 'CurrentRegion' come from?


"Nigel" <nigel-sw@suxnospampanet.com> wrote in message
news:e1uErv65FHA.2628@TK2MSFTNGP11.phx.gbl...
> Dim LastRow as Long
> LastRow = Sheets("Database").Cells(Rows.Count,1).End(xlup).Row
>
>
> With Sheets("Database").Select
> Range("A1:T" & LastRow).AdvancedFilter Action:=xlFilterCopy,
>
> CriteriaRange:=Range( _
> "V1:V2"), CopyToRange:=Range("X1:AQ1"), Unique:=False
> Range("A1").Select
>
> --
> Cheers
> Nigel
>
>
>
> "John" <r.@9999yahoo.co.uk> wrote in message
> news:dl57c9$i30$1@reader01.news.esat.net...
>> I've applied an Advanced Filter to a Range and simply recorded the macro,
>> extract below, but my problem is that my data in the Database sheet is
>> dynamic and changes in Row length (not by column)
>>
>> How can I incorporate a dynamic range via VBA with Advanced Filters?
>>
>> Thanks
>>
>>
>>
>> Sheets("Database").Select
>> Range("A1:T61").AdvancedFilter Action:=xlFilterCopy,
>> CriteriaRange:=Range( _
>> "V1:V2"), CopyToRange:=Range("X1:AQ1"), Unique:=False
>> Range("A1").Select
>>
>>
>
>

Guys got it working, must have paste it incorrectly

Thanks


"John" <r.@9999yahoo.co.uk> wrote in message
news:dl5htj$li2$1@reader01.news.esat.net...
> Guys I'm getting a syntax error on both your codes, am I missing
> something? Where does the 'CurrentRegion' come from?
>
>
> "Nigel" <nigel-sw@suxnospampanet.com> wrote in message
> news:e1uErv65FHA.2628@TK2MSFTNGP11.phx.gbl...
>> Dim LastRow as Long
>> LastRow = Sheets("Database").Cells(Rows.Count,1).End(xlup).Row
>>
>>
>> With Sheets("Database").Select
>> Range("A1:T" & LastRow).AdvancedFilter Action:=xlFilterCopy,
>>
>> CriteriaRange:=Range( _
>> "V1:V2"), CopyToRange:=Range("X1:AQ1"), Unique:=False
>> Range("A1").Select
>>
>> --
>> Cheers
>> Nigel
>>
>>
>>
>> "John" <r.@9999yahoo.co.uk> wrote in message
>> news:dl57c9$i30$1@reader01.news.esat.net...
>>> I've applied an Advanced Filter to a Range and simply recorded the
>>> macro,
>>> extract below, but my problem is that my data in the Database sheet is
>>> dynamic and changes in Row length (not by column)
>>>
>>> How can I incorporate a dynamic range via VBA with Advanced Filters?
>>>
>>> Thanks
>>>
>>>
>>>
>>> Sheets("Database").Select
>>> Range("A1:T61").AdvancedFilter Action:=xlFilterCopy,
>>> CriteriaRange:=Range( _
>>> "V1:V2"), CopyToRange:=Range("X1:AQ1"), Unique:=False
>>> Range("A1").Select
>>>
>>>
>>
>>
>
>

I didn't understand the criteria. Why V1:V2??