(Solved) – Excel VBA Autofill not populating first row and column


I need to copy a formula from one cell into a block of cells. I have tried using AutoFill, which works to a degree, but it does not populate the first row or the first column.

My formula in C4 should be copied to the entire block C4:X12. (In real life it is a sumproduct.)

Sub GetUniqueLocationsTEST()
    Dim LastRow As Long
    Dim LastCol As Long

    With thisWorkbook.Worksheets("sheet2")
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).row
        LastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column
        .Range("C4").formula = "=1 1"
        .Range("C4").AutoFill Destination:=.Range("C4:X12") ', .Cells(LastRow, LastCol))
    End With
End Sub

Results in C4 showing “2”, and the range D5:X12 correctly showing “2”.
BUT D4:X4 and C5:C12 are blank – nothing is copied there! If I put other random text into these cells it is still there after the Sub is run.

In the sample code you’ll see that I’ve even forced the destination range to a literal.

Perhaps Autofill is not the correct method? Should I be using Copy? Or something else?

Thanks in advance.

Leave a Reply

Your email address will not be published. Required fields are marked *