Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    VBA Advanced filter (Excel XP)

    Hi,

    I made a macro using the recorder for using advanced filter. I noticed that if you use it manual that it automatically selects the complete list. I hoped that after recording the macro that would stay the same but alas the macro stored the range it first selected.

    Here follows the line from the code i have. I would like to know how i should change it to have the selection flexible. The cell A4 is fixed. The column F is fixed as well but the row 49 is ever expanding.

    Range("A4:F49").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("O1:O2"), CopyToRange:=Range("H4:M4"), Unique:=False

    Regards Forbaty

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Advanced filter (Excel XP)

    Assuming that column G is blank, you could replace Range("A4:F49") with Range("A4").CurrentRegion.

  3. #3
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VBA Advanced filter (Excel XP)

    Hi Hans,

    This works great. Thanks for the help.

    Regards Forbaty

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •