Thread: Autofill using Dynamic Range (Last Column) and Last Row

Hello, I have some issues. Beyond that, Excel VBA is misbehaving.

What I am trying to accomplish: I would like to select a dynamic range, based of last column, last column + 16, etc., then to autofill that range to the last row.

Here is what I have:

    Dim LC As Integer
    LC = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Dim LR As Long
    LR = Cells(Rows.Count, 1).End(xlUp).Row

    Range(Cells(2, LC + 1), Cells(2, LC + 16)).AutoFill Destination:=Range(Cells(2, LC + 1), Cells(2, LC + 16) & LR)

This doesn't work. I wouldn't be opposed to using AutoFill with Selection, if you could make that work when Selection is a range. I could then just select the range above, and autofill down to LastRow.

Something like:

Range(Cells(2, LC + 1), Cells(2, LC + 16)).Select
Selection.AutoFill Destination:=Range(Selection & LR)

That doesn't work, either (Type mismatch error). I know it's because I do not know the proper syntax for AutoFill, and the problem is that I am having a hard time finding a solution that uses dynamic columns and rows with autofill.

Any help is greatly appreciated. Thank you in advance.

Sorry for not being as clear as I could have. I will give a bit more of an explanation.

I generate a spreadsheet that has a number of columns and rows. That number changes every time I generate the spreadsheet. In the first empty column Cell(1, LR + 1) I enter a title via macro. In the row below, Cell(2, LR + 1) I enter a formula via macro. It looks something like this:

Cells(1, LC + 1).FormulaR1C1 = "Shipments"
Cells(2, LC + 1).FormulaR1C1 = _
        "=VLOOKUP(RC[-71],'Lookup Table'!C[-71]:C[-70],2,FALSE)"

I do this for a range, one column at a time, for the first 16 Columns after the Last Column of data in Row 1. What this leaves me with is 16 columns with row 1 being titles and row 2 being formulas (and some empty cells). The location of these columns will change every time I generate these spreadsheets.

What I want to do next is copy row 2 of those 16 columns down to the last row of data in column 1, or "A". I have attached a dummy sheet for clarification.

Edit: After using your code:

Range(Cells(2, LC + 1), Cells(2, LC + 16)).AutoFill Destination:=Range(Cells(2, LC + 1), Cells(LR, LC + 16))

It worked beautifully!!! I have been wracking my brain over this, and it was such a simple fix. Thank you!