Determine last row in columns 1-4 to copy a formula in column 5

I am importing external data into a workbook with formulas.
I want to automate the copy-down for the formula in cell E1
to the last populated row in columns 1-4.

One way:

Option Explicit
Sub testme()

Dim LastRow As Long
Dim iCol As Long

LastRow = 0
With Worksheets("sheet1")
For iCol = 1 To 4
LastRow = Application.Max(LastRow, _
.Cells(.Rows.Count, iCol).End(xlUp).Row)
Next iCol

.Range("e1:e" & LastRow).Formula = "=A1&b1&c1&d1"
'or maybe:
.Range("e1:e" & LastRow).FormulaR1C1 = .Range("e1").FormulaR1C1

End With

End Sub

GEB wrote:
>
> I am importing external data into a workbook with formulas.
> I want to automate the copy-down for the formula in cell E1
> to the last populated row in columns 1-4.

--

Dave Peterson

Dave,

Thank you.
I have tried both methods successfully.

GEB

"Dave Peterson" wrote:

> One way:
>
> Option Explicit
> Sub testme()
>
> Dim LastRow As Long
> Dim iCol As Long
>
> LastRow = 0
> With Worksheets("sheet1")
> For iCol = 1 To 4
> LastRow = Application.Max(LastRow, _
> .Cells(.Rows.Count, iCol).End(xlUp).Row)
> Next iCol
>
> .Range("e1:e" & LastRow).Formula = "=A1&b1&c1&d1"
> 'or maybe:
> .Range("e1:e" & LastRow).FormulaR1C1 = .Range("e1").FormulaR1C1
>
> End With
>
> End Sub
>
> GEB wrote:
> >
> > I am importing external data into a workbook with formulas.
> > I want to automate the copy-down for the formula in cell E1
> > to the last populated row in columns 1-4.
>
> --
>
> Dave Peterson
>