[Solved] VBA: AutoFill using dynamic "Destination"

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.

  • Please see the attached .xls

    It uses the WorksheetChange() event to redimension a range [Data] as new items are added to the bottom of the list in column A, then AutoFill the formulas down column B.
    As it stands, the code works fine, but I’d like to extend the AutoFill range to allow for additional columns C, D, E….etc.
    [FillStart] is the range of cells from B1 to F1, and grows/shrinks as columns are added/deleted

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
    End If
    End Sub

    Public Sub Fill_It_Down()

    Set Data = Range([topoflist], [A65536].End(xlUp))
    Set First = [topoflist].Offset(0, 1)
    Set Fillstart = Range([First], [First].End(xlToRight))
    Coli = [Fillstart].Columns.Count
    Rowi = [Data].Rows.Count
    If Rowi > 1 Then
    [First].AutoFill Destination:=[Data].Offset(0, 1) < How to change this line???
    End If

    End Sub

    How should I specify the Destination part to allow for an expanding [FillStart] range?


    Varium et mutabile semper Excel


    &amp;quot;Varium et mutabile semper Excel&amp;quot;

  • Chris,

    Not 100% sure without running the code but would changing

    [First].AutoFill Destination:=[Data].Offset(0, 1)


    [First].AutoFill Destination:=[Data].Offset(0, Coli)

    do what you want?


  • Hi Pesky, thanks for replying :wink2:

    Alas, I've tried that, and it bombs out on that line with "AutoFill Method of Range class failed" :(

    Same thing if I use both Rowi and Coli on the Offset().

    Any other thoughts?



    &amp;quot;Varium et mutabile semper Excel&amp;quot;

  • Hi Chris,

    How about:


  • Brilliant Ritchie! :congrats:

    The solution was right in front of my eyes;

    Set Fillstart = Range([First], [First].End(xlToRight)) :duh:

    Thanx M8!


    &amp;quot;Varium et mutabile semper Excel&amp;quot;

  • Hmmmmmmm!

    Nice Once Richie - that works.

    I cant see why mine wont though. The VBA shows that Coli is returning 5. I even tried
    [First].AutoFill Destination:=[Data].Offset(0, [Fillstart].Columns.Count)

    which is similar (but not exact) to an example in the VBA help files.
    Any idea Richie????


  • Hi Weasel,

    OK, consider this:

    1. From the VBE Help files on AutoFill - "The destination must include the source range"

    2. Use a MessageBox (or your preferred method) to show the address of the Destination range that you suggest.

    Does that help?

    Also, we are looking to AutoFill a number of columns rather than just one so we would need to amend the [First] startpoint. How about:

    [First].Resize(, Coli).AutoFill _
    Destination:=Range([First].Resize(, Coli), [First].Offset(Rowi - 1).Resize(, Coli))

    Probably NOT the easiest code in the world to understand ;;)

  • Quote

    Originally posted by Richie(UK)
    Probably NOT the easiest code in the world to understand ;;)

    Nope, i like yours better.

    thx alot

Participate now!

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