| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Advanced Filter to Dynamic Range
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Advanced Filter to Dynamic Range
Dim LastRow as Long
LastRow = Sheets("Database").Cells(Rows.Count,1).End(xlup).R ow 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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Advanced Filter to Dynamic Range
Thanks Guys
"Nigel" wrote in message ... Dim LastRow as Long LastRow = Sheets("Database").Cells(Rows.Count,1).End(xlup).R ow 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Advanced Filter to Dynamic Range
Guys I'm getting a syntax error on both your codes, am I missing something?
Where does the 'CurrentRegion' come from? "Nigel" wrote in message ... Dim LastRow as Long LastRow = Sheets("Database").Cells(Rows.Count,1).End(xlup).R ow 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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Advanced Filter to Dynamic Range
Guys got it working, must have paste it incorrectly
Thanks "John" wrote in message ... Guys I'm getting a syntax error on both your codes, am I missing something? Where does the 'CurrentRegion' come from? "Nigel" wrote in message ... Dim LastRow as Long LastRow = Sheets("Database").Cells(Rows.Count,1).End(xlup).R ow 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Advanced Filter using a range name as the criteria | Excel Worksheet Functions | |||
| Advanced filter a dynamic date range | Excel Worksheet Functions | |||
| advanced filter a range | Excel Worksheet Functions | |||
| "Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
| Advanced Filter & Named Range | Excel Programming | |||
Linear Mode
