Limited byrows propagate a value via macro to all cells in column

I need to propagate the value "1" to all cells in a column via macro. The
number of cells will vary based on the data loaded at file open. I do not
want to create additional rows by inserting a formula and copying it to an
entire column. I am using Excel 2003.

Let's say you want to insert 1's into the first open column, and match the size to the number of
rows in column A:

Cells(1, Columns.Count).End(xlToLeft)(1, 2).Resize _
(Cells(Rows.Count, 1).End(xlUp).Row).Value = 1

HTH,
Bernie
MS Excel MVP


"David" <David@discussions.microsoft.com> wrote in message
news:5A874C5F-DA18-4718-B7D6-7B020ADB9569@microsoft.com...
>I need to propagate the value "1" to all cells in a column via macro. The
> number of cells will vary based on the data loaded at file open. I do not
> want to create additional rows by inserting a formula and copying it to an
> entire column. I am using Excel 2003.

"Bernie Deitrick" wrote:

> Let's say you want to insert 1's into the first open column, and match the size to the number of
> rows in column A:
>
> Cells(1, Columns.Count).End(xlToLeft)(1, 2).Resize _
> (Cells(Rows.Count, 1).End(xlUp).Row).Value = 1
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "David" <David@discussions.microsoft.com> wrote in message
> news:5A874C5F-DA18-4718-B7D6-7B020ADB9569@microsoft.com...
> >I need to propagate the value "1" to all cells in a column via macro. The
> > number of cells will vary based on the data loaded at file open. I do not
> > want to create additional rows by inserting a formula and copying it to an
> > entire column. I am using Excel 2003.
>
>
> Thank you Bernie, I hope it works. I will try it. Can you give me advice as to how to implement this solution.
Is there an area where I key it in so that it will execute when the data is
loaded or do I have to activate it in some other way?
Thank you for your help.

David,

>Thank you Bernie, I hope it works. I will try it. Can you give me advice as to how to implement
>this solution.
> Is there an area where I key it in so that it will execute when the data is
> loaded or do I have to activate it in some other way?

Try reading:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

HTH,
Bernie
MS Excel MVP


"David" <David@discussions.microsoft.com> wrote in message
news:082DD867-CF42-4C9C-BA11-4CDF022EC97F@microsoft.com...
>
>
> "Bernie Deitrick" wrote:
>
>> Let's say you want to insert 1's into the first open column, and match the size to the number of
>> rows in column A:
>>
>> Cells(1, Columns.Count).End(xlToLeft)(1, 2).Resize _
>> (Cells(Rows.Count, 1).End(xlUp).Row).Value = 1
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "David" <David@discussions.microsoft.com> wrote in message
>> news:5A874C5F-DA18-4718-B7D6-7B020ADB9569@microsoft.com...
>> >I need to propagate the value "1" to all cells in a column via macro. The
>> > number of cells will vary based on the data loaded at file open. I do not
>> > want to create additional rows by inserting a formula and copying it to an
>> > entire column. I am using Excel 2003.
>>
>>
>>

"Bernie Deitrick" wrote:

> David,
>
> >Thank you Bernie, I hope it works. I will try it. Can you give me advice as to how to implement
> >this solution.
> > Is there an area where I key it in so that it will execute when the data is
> > loaded or do I have to activate it in some other way?
>
> Try reading:
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "David" <David@discussions.microsoft.com> wrote in message
> news:082DD867-CF42-4C9C-BA11-4CDF022EC97F@microsoft.com...
> >
> >
> > "Bernie Deitrick" wrote:
> >
> >> Let's say you want to insert 1's into the first open column, and match the size to the number of
> >> rows in column A:
> >>
> >> Cells(1, Columns.Count).End(xlToLeft)(1, 2).Resize _
> >> (Cells(Rows.Count, 1).End(xlUp).Row).Value = 1
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "David" <David@discussions.microsoft.com> wrote in message
> >> news:5A874C5F-DA18-4718-B7D6-7B020ADB9569@microsoft.com...
> >> >I need to propagate the value "1" to all cells in a column via macro. The
> >> > number of cells will vary based on the data loaded at file open. I do not
> >> > want to create additional rows by inserting a formula and copying it to an
> >> > entire column. I am using Excel 2003.
> >>
> >>
> >>
>
> THANK YOU BERNIE THAT IS JUST WHAT I NEED.

>> THANK YOU BERNIE THAT IS JUST WHAT I NEED.

You're welcome....

Bernie
MS Excel MVP