Using Varying Range Size in Macro

I'm trying to create a macro that does the following steps.

1. Inserts a column in front of an existing column of data.
2. Enters a constant value in each cell of the new column for every
filled cell of the existing column.

I can do this the first time. However, I don't know how to make the
macro dynamic to take into account varying lengths of the existing
column. It may be 10 rows one time and 20 rows the second time.

The data might look like this:
Column A
Row 1 $100
Row 2 $200
Row 3 $300

After the macro, the data would look like this:
Column A Column B
Row 1 1234 $100
Row 2 1234 $200
Row 3 1234 $300

The next time, there will be more or less rows in Column A.

Thank you for your help.

Try something like

With ActiveCell
Columns(.Column).Insert
Cells(1, .Column - 1).Value = 1234
iLastRow = Cells(Rows.Count, .Column).End(xlUp).Row
Cells(1, .Column - 1).AutoFill Range(Cells(1, .Column - 1),
Cells(iLastRow, .Column - 1))
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Charles Allen" <[email protected]> wrote in message
news:[email protected]...
> I'm trying to create a macro that does the following steps.
>
> 1. Inserts a column in front of an existing column of data.
> 2. Enters a constant value in each cell of the new column for every
> filled cell of the existing column.
>
> I can do this the first time. However, I don't know how to make the
> macro dynamic to take into account varying lengths of the existing
> column. It may be 10 rows one time and 20 rows the second time.
>
> The data might look like this:
> Column A
> Row 1 $100
> Row 2 $200
> Row 3 $300
>
> After the macro, the data would look like this:
> Column A Column B
> Row 1 1234 $100
> Row 2 1234 $200
> Row 3 1234 $300
>
> The next time, there will be more or less rows in Column A.
>
> Thank you for your help.
>