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.

Using Varying Range Size in Macro
 

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" wrote in message
...
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.