set rng = Range(cells(1,1),cells(rows.count,1).End(xlup))
rng.offset(0,2).Formula = "=A1&B1"

--
Regards,
Tom Ogilvy

"Tuchik" wrote in message
...
Okay here is the situation: I have a worksheet with 4 columns and X rows.
I
have a macro to concatenate column A and B and then autofill the formula
through 10,000 rows. This is because the spreadsheet may contain anywhere
from 1 to 10,000 records. I want to change this so that the autofill only
concatenates records where there is actually ane entry in the A and B
columns. So I added the following code to promt the user for the number
of
records:

Dim intRecords As Integer
intRecords = InputBox("How many records are in this batch?", "Number
of
Records")

No I just need to get this info. into the autofill part of the macro, so I
tried simply modifying the code like this:

ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-13],RC[-12])"
Selection.AutoFill Destination:=Range("N2:NintRecords"),
Type:=xlFillDefault

Of course, it's simply not that easy. "NintRecords" does not make sense
to
excel for the autofill and I get a request to debug if I try to run the
macro. So what do I need to do to get the autofill to copy the formula
into
only the cells specified by my intRecords?

Thanks in advance for the help.

Jason