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.

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

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

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

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