Fill the column with the last number

[sensor]

I have 2 columns. Cloumn A has 1 to 100 column B some times has upto 50 rows
filled or sometimes upto 80 ans ometimes upto 100 rows filled.

When Column B is not completley filled how to fill the empty column with the
last entry in that column

for example 1.
cloumn B ends in row # 80 with a number 657 I want to fill the rest of the
rows in column B with 657 up to row #100.

for example 2.
cloumn B ends in row # 60 with a number 6257 I want to fill the rest of the
rows in column B with 6257 up to row #100.

[Richard Buttrey]

On Mon, 1 May 2006 13:03:02 -0700, sensor
<sensor@discussions.microsoft.com> wrote:

>I have 2 columns. Cloumn A has 1 to 100 column B some times has upto 50 rows
>filled or sometimes upto 80 ans ometimes upto 100 rows filled.
>
>When Column B is not completley filled how to fill the empty column with the
>last entry in that column
>
>for example 1.
>cloumn B ends in row # 80 with a number 657 I want to fill the rest of the
>rows in column B with 657 up to row #100.


How about B81, =B80 and copied down to B100

>for example 2.
>cloumn B ends in row # 60 with a number 6257 I want to fill the rest of the
>rows in column B with 6257 up to row #100.

ditto B61, =B60 and copied down.

Or am I missing something????

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[sensor]

Thanks for the reply Richard,

Actually I am trying to write a macro . and since I do not know everytime
where does the row ends. so I want to fill the empty rows in column B with
the last number appeared on that column
Thanks



"Richard Buttrey" wrote:

> On Mon, 1 May 2006 13:03:02 -0700, sensor
> <sensor@discussions.microsoft.com> wrote:
>
> >I have 2 columns. Cloumn A has 1 to 100 column B some times has upto 50 rows
> >filled or sometimes upto 80 ans ometimes upto 100 rows filled.
> >
> >When Column B is not completley filled how to fill the empty column with the
> >last entry in that column
> >
> >for example 1.
> >cloumn B ends in row # 80 with a number 657 I want to fill the rest of the
> >rows in column B with 657 up to row #100.
>
>
> How about B81, =B80 and copied down to B100
>
> >for example 2.
> >cloumn B ends in row # 60 with a number 6257 I want to fill the rest of the
> >rows in column B with 6257 up to row #100.
>
> ditto B61, =B60 and copied down.
>
> Or am I missing something????
>
> Rgds
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________
>

[Gord Dibben]

Manually...............

Select the two columns.

F5>Special>Blanks>OK

Enter an = sign in active cell.

Point to cell above and hit CTRL + ENTER.

You can leave the formulas as is or paste special>values.

Record a macro while doing the steps.

Columns("A:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"


Gord Dibben MS Excel MVP

On Tue, 2 May 2006 05:28:01 -0700, sensor <sensor@discussions.microsoft.com>
wrote:

>Thanks for the reply Richard,
>
>Actually I am trying to write a macro . and since I do not know everytime
>where does the row ends. so I want to fill the empty rows in column B with
>the last number appeared on that column
>Thanks
>
>
>
>"Richard Buttrey" wrote:
>
>> On Mon, 1 May 2006 13:03:02 -0700, sensor
>> <sensor@discussions.microsoft.com> wrote:
>>
>> >I have 2 columns. Cloumn A has 1 to 100 column B some times has upto 50 rows
>> >filled or sometimes upto 80 ans ometimes upto 100 rows filled.
>> >
>> >When Column B is not completley filled how to fill the empty column with the
>> >last entry in that column
>> >
>> >for example 1.
>> >cloumn B ends in row # 80 with a number 657 I want to fill the rest of the
>> >rows in column B with 657 up to row #100.
>>
>>
>> How about B81, =B80 and copied down to B100
>>
>> >for example 2.
>> >cloumn B ends in row # 60 with a number 6257 I want to fill the rest of the
>> >rows in column B with 6257 up to row #100.
>>
>> ditto B61, =B60 and copied down.
>>
>> Or am I missing something????
>>
>> Rgds
>>
>> __
>> Richard Buttrey
>> Grappenhall, Cheshire, UK
>> __________________________
>>

[sensor]

The first part I understood but the 3 line commands you have asked me to do
while recording i am not able to understand. Can you please tell me how do I
get to line 2 and 3 in the command
> Selection.SpecialCells(xlCellTypeBlanks).Select
> Selection.FormulaR1C1 = "=R[-1]C"

Thank you

"Gord Dibben" wrote:

> Manually...............
>
> Select the two columns.
>
> F5>Special>Blanks>OK
>
> Enter an = sign in active cell.
>
> Point to cell above and hit CTRL + ENTER.
>
> You can leave the formulas as is or paste special>values.
>
> Record a macro while doing the steps.
>
> Columns("A:B").Select
> Selection.SpecialCells(xlCellTypeBlanks).Select
> Selection.FormulaR1C1 = "=R[-1]C"
>
>
> Gord Dibben MS Excel MVP
>
> On Tue, 2 May 2006 05:28:01 -0700, sensor <sensor@discussions.microsoft.com>
> wrote:
>
> >Thanks for the reply Richard,
> >
> >Actually I am trying to write a macro . and since I do not know everytime
> >where does the row ends. so I want to fill the empty rows in column B with
> >the last number appeared on that column
> >Thanks
> >
> >
> >
> >"Richard Buttrey" wrote:
> >
> >> On Mon, 1 May 2006 13:03:02 -0700, sensor
> >> <sensor@discussions.microsoft.com> wrote:
> >>
> >> >I have 2 columns. Cloumn A has 1 to 100 column B some times has upto 50 rows
> >> >filled or sometimes upto 80 ans ometimes upto 100 rows filled.
> >> >
> >> >When Column B is not completley filled how to fill the empty column with the
> >> >last entry in that column
> >> >
> >> >for example 1.
> >> >cloumn B ends in row # 80 with a number 657 I want to fill the rest of the
> >> >rows in column B with 657 up to row #100.
> >>
> >>
> >> How about B81, =B80 and copied down to B100
> >>
> >> >for example 2.
> >> >cloumn B ends in row # 60 with a number 6257 I want to fill the rest of the
> >> >rows in column B with 6257 up to row #100.
> >>
> >> ditto B61, =B60 and copied down.
> >>
> >> Or am I missing something????
> >>
> >> Rgds
> >>
> >> __
> >> Richard Buttrey
> >> Grappenhall, Cheshire, UK
> >> __________________________
> >>
>
>

[Gord Dibben]

I apologize for the lack of clarity.

The 3 lines of code came from me recording a macro while doing the "first part".

Should read..........

Sub CopyNums()
Columns("A:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End Sub


Gord

On Tue, 2 May 2006 13:22:01 -0700, sensor <sensor@discussions.microsoft.com>
wrote:

>The first part I understood but the 3 line commands you have asked me to do
>while recording i am not able to understand. Can you please tell me how do I
>get to line 2 and 3 in the command
>> Selection.SpecialCells(xlCellTypeBlanks).Select
>> Selection.FormulaR1C1 = "=R[-1]C"
>
>Thank you
>
>"Gord Dibben" wrote:
>
>> Manually...............
>>
>> Select the two columns.
>>
>> F5>Special>Blanks>OK
>>
>> Enter an = sign in active cell.
>>
>> Point to cell above and hit CTRL + ENTER.
>>
>> You can leave the formulas as is or paste special>values.
>>
>> Record a macro while doing the steps.
>>
>> Columns("A:B").Select
>> Selection.SpecialCells(xlCellTypeBlanks).Select
>> Selection.FormulaR1C1 = "=R[-1]C"
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Tue, 2 May 2006 05:28:01 -0700, sensor <sensor@discussions.microsoft.com>
>> wrote:
>>
>> >Thanks for the reply Richard,
>> >
>> >Actually I am trying to write a macro . and since I do not know everytime
>> >where does the row ends. so I want to fill the empty rows in column B with
>> >the last number appeared on that column
>> >Thanks
>> >
>> >
>> >
>> >"Richard Buttrey" wrote:
>> >
>> >> On Mon, 1 May 2006 13:03:02 -0700, sensor
>> >> <sensor@discussions.microsoft.com> wrote:
>> >>
>> >> >I have 2 columns. Cloumn A has 1 to 100 column B some times has upto 50 rows
>> >> >filled or sometimes upto 80 ans ometimes upto 100 rows filled.
>> >> >
>> >> >When Column B is not completley filled how to fill the empty column with the
>> >> >last entry in that column
>> >> >
>> >> >for example 1.
>> >> >cloumn B ends in row # 80 with a number 657 I want to fill the rest of the
>> >> >rows in column B with 657 up to row #100.
>> >>
>> >>
>> >> How about B81, =B80 and copied down to B100
>> >>
>> >> >for example 2.
>> >> >cloumn B ends in row # 60 with a number 6257 I want to fill the rest of the
>> >> >rows in column B with 6257 up to row #100.
>> >>
>> >> ditto B61, =B60 and copied down.
>> >>
>> >> Or am I missing something????
>> >>
>> >> Rgds
>> >>
>> >> __
>> >> Richard Buttrey
>> >> Grappenhall, Cheshire, UK
>> >> __________________________
>> >>
>>
>>

Gord Dibben MS Excel MVP

[Richard Buttrey]

Ah, OK.

In testing this I discovered that the SpecialCells(xlCellTypeBlanks)
command only seems to work if either there has previously been data in
B1:B100, or if there is a value in say B101 to delimit the list. Odd
that....

So I suggest the following lines of code

Sub CopytoBlanks
On Error Resume Next
Range("B101") = "last"
Range("B1:B100").SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
Range("B101").ClearContents
End Sub


The On error line will handle the condition where all of B1:B100 is
completely filled.

HTH




On Tue, 2 May 2006 05:28:01 -0700, sensor
<sensor@discussions.microsoft.com> wrote:

>Thanks for the reply Richard,
>
>Actually I am trying to write a macro . and since I do not know everytime
>where does the row ends. so I want to fill the empty rows in column B with
>the last number appeared on that column
>Thanks
>
>
>
>"Richard Buttrey" wrote:
>
>> On Mon, 1 May 2006 13:03:02 -0700, sensor
>> <sensor@discussions.microsoft.com> wrote:
>>
>> >I have 2 columns. Cloumn A has 1 to 100 column B some times has upto 50 rows
>> >filled or sometimes upto 80 ans ometimes upto 100 rows filled.
>> >
>> >When Column B is not completley filled how to fill the empty column with the
>> >last entry in that column
>> >
>> >for example 1.
>> >cloumn B ends in row # 80 with a number 657 I want to fill the rest of the
>> >rows in column B with 657 up to row #100.
>>
>>
>> How about B81, =B80 and copied down to B100
>>
>> >for example 2.
>> >cloumn B ends in row # 60 with a number 6257 I want to fill the rest of the
>> >rows in column B with 6257 up to row #100.
>>
>> ditto B61, =B60 and copied down.
>>
>> Or am I missing something????
>>
>> Rgds
>>
>> __
>> Richard Buttrey
>> Grappenhall, Cheshire, UK
>> __________________________
>>

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________