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

    Code
    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

    Code
    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!

Register Yourself Login