Tags: advanced, applied, below, database, dynamic, excel, extract, filter, ive, macro, microsoft, msdn, range, recorded, sheet, simply, software

Advanced Filter to Dynamic Range

On Microsoft » Microsoft Excel

4,375 words with 5 Comments; publish: Fri, 06 Jun 2008 12:05:00 GMT; (30646.88, « »)

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

All Comments

Leave a comment...

  • 5 Comments
    • 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..excel.todaysummary.com.9999yahoo.co.uk> wrote in message

      news:dl57c9$i30$1.excel.todaysummary.com.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

      >

      #1; Fri, 06 Jun 2008 12:06:00 GMT
    • 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" <r..excel.todaysummary.com.9999yahoo.co.uk> wrote in message

      news:dl57c9$i30$1.excel.todaysummary.com.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

      >

      #2; Fri, 06 Jun 2008 12:07:00 GMT
    • Thanks Guys

      "Nigel" <nigel-sw.excel.todaysummary.com.suxnospampanet.com> wrote in message

      news:e1uErv65FHA.2628.excel.todaysummary.com.TK2MSFTNGP11.phx.gbl...

      > 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" <r..excel.todaysummary.com.9999yahoo.co.uk> wrote in message

      > news:dl57c9$i30$1.excel.todaysummary.com.reader01.news.esat.net...

      >

      #3; Fri, 06 Jun 2008 12:08:00 GMT
    • Guys I'm getting a syntax error on both your codes, am I missing something?

      Where does the 'CurrentRegion' come from?

      "Nigel" <nigel-sw.excel.todaysummary.com.suxnospampanet.com> wrote in message

      news:e1uErv65FHA.2628.excel.todaysummary.com.TK2MSFTNGP11.phx.gbl...

      > 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" <r..excel.todaysummary.com.9999yahoo.co.uk> wrote in message

      > news:dl57c9$i30$1.excel.todaysummary.com.reader01.news.esat.net...

      >

      #4; Fri, 06 Jun 2008 12:09:00 GMT
    • Guys got it working, must have paste it incorrectly

      Thanks

      "John" <r..excel.todaysummary.com.9999yahoo.co.uk> wrote in message

      news:dl5htj$li2$1.excel.todaysummary.com.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.excel.todaysummary.com.suxnospampanet.com> wrote in message

      > news:e1uErv65FHA.2628.excel.todaysummary.com.TK2MSFTNGP11.phx.gbl...

      >

      #5; Fri, 06 Jun 2008 12:10:00 GMT