I think you want the last row of Column C since there is no data in column D
until you put in the formula

LastRow = Range("C" & Rows.Count).end(xlup).Row
Range("D2").FormulaR1C1 = "=RC[-1]&"" ""&RC[4]"
Range("D2").AutoFill Destination:=Range("D2:D" & LastRow)


"Shams" wrote:

Folks,
I have the following Macro Script generated by recording Excel steps:

Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]&"" ""&RC[4]"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D14")
Range("D2:D14").Select

This is basically concatenating the content of 2 other cells in D2 and then
copying down the formula....
as you can see when I am recording the copy down step by doubleclicking into
the corner of cell D2 it is copying until D14 which happens to be the end of
the current data range....

Now, with this structure it will always stop at D14 regardless of the number
of rows...is there anyway I can add a syntax so that the macro will
automatically evaluate to the last row of Column D to autoFill the formula?

Thanks in advance for your help.

Regards,
Shams.