CODEWITHSUNDEEP
excelvba
Hurk
Hurk

Reputation: 19

How can I create a macro to autofill data from the top row down to the last adjacent column with data in it?

I need to create a macro that can take data in columns F through I and autofill it down to the last cell in column E that has data in it. The code below almost does what I need it to do. However, I want to be able to use this macro with different data further down in columns F:I

Dim lastrow As Long

lastrow = Range("e" & Rows.Count).End(xlUp).Row

Dim x(1 To 4) As Variant

With ThisWorkbook.Sheets("sheet1")
    x(1) = Range("f2")
    x(2) = Range("g2")
    x(3) = Range("H2")
    x(4) = Range("I2")

    .Range("F3:i3").Formula = x
    .Range("f3:i" & lastrow).FillDown
End With

Upvotes: 1

Views: 419

Answers (1)

ko_00
ko_00

Reputation: 118

Like this:

With ThisWorkbook.Sheets("sheet1")
    lastrow = Range("E" & Rows.Count).End(xlUp).Row
    
    For i = 3 To lastrow
        .Range("F" & i).Formula = .Range("F2").Formula
        .Range("G" & i).Formula = .Range("G2").Formula
        .Range("H" & i).Formula = .Range("H2").Formula
        .Range("I" & i).Formula = .Range("I2").Formula
    Next i
    
End With

Upvotes: 3

Related Questions