Advanced Filter to Dynamic Range

  • Thread starter Thread starter John
  • Start date Start date
J

John

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
 
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
 
Thanks Guys

Nigel said:
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
Click to expand...
 
Guys I'm getting a syntax error on both your codes, am I missing something?
Where does the 'CurrentRegion' come from?
 
Back
Top