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

1
votes

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. How can i do this?

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

1
excelvba

1 Answers

2
votes

Like this:? :D 

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