Advanced Filter with dynamic CopyToRange

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Hello Everyone,

    Using Advanced Filter

    How would you make the CopyToRange dynamic?

    Per the code below it works fine, but instead of a static "J7:CF7" I need CF to be able to expand to the furthest right column in Row 7 that holds a value.

    For instance sometimes it is J7:CF7, othertimes it is J7:FD7

    Sheet19.Range("F7").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheet16.Range("J1:AJ2"), CopyToRange:=Sheet16.Range("J7:CF7"), Unique:=False

    Thanks for any help with this

  • How about

    Sub sleeplol()
       Dim Rng As Range
       With Sheet16
          Set Rng = .Range("J7", .Cells(7, Columns.Count).End(xlToLeft))
       End With
       Sheet19.Range("F7").CurrentRegion.AdvancedFilter xlFilterCopy, Sheet16.Range("J1:AJ2"), Rng, False
    End Sub
  • Darn, it does work lol

    I made a dummy workbook to send you; when I tested it, your macro works perfectly. Even when expanding the CopyToRange; awesome!

    But for some reason it's not working with my actual WB. I'll dig into why. Though I'd like to, I can't send the whole book over due to employee info.

    At least I know that your script works though; process of elimination

    As always, thanks again Fluff

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!