Advanced Filter with dynamic CopyToRange

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