I'm trying to create a macro to preformat an imported text
file. One of the tasks is to concatenate lastname,
firstname, middleinit, suffix that is spread into 4
columns into one column. The number of rows varies with
the imported text file.

I've tried inserting a blank column and building the
concat formula in the top cell, but I'm having a problem
copying this formula down to all the cells below while
STOPPING at the last cell that has data to the right.

Suggestions for this newbie, please?

Jim,

Assuming you have just entered the formula in A1, and A1 (or A2, or
Awhatever) is the active cell, the following will do it:

i = 1
Do Until IsEmpty(ActiveCell.Offset(i,1))
ActiveCell.Offset(i,0).FormulaR1C1 = ActiveCell.FormulaR1C1
i = i + 1
Loop

HTH,
Nikos

"Jim Clark" wrote in message
...
I'm trying to create a macro to preformat an imported text
file. One of the tasks is to concatenate lastname,
firstname, middleinit, suffix that is spread into 4
columns into one column. The number of rows varies with
the imported text file.

I've tried inserting a blank column and building the
concat formula in the top cell, but I'm having a problem
copying this formula down to all the cells below while
STOPPING at the last cell that has data to the right.

Suggestions for this newbie, please?