CODEWITHSUNDEEP
excelvba
DerekBez
DerekBez

Reputation: 15

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?

Upvotes: 0

Views: 579

Answers (1)

BigBen
BigBen

Reputation: 50008

You probably don't need AutoFill. You can write a formula to an entire range in one go, and Excel will update relative references if needed:

.Range("C4", .Cells(LastRow, LastCol)).Formula = your formula

Upvotes: 2

Related Questions