Fill the column with the last number
 

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.

Fill the column with the last number
 

On Mon, 1 May 2006 13:03:02 -0700, sensor
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
__________________________

Fill the column with the last number
 

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
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
__________________________

Fill the column with the last number
 

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

Select the two columns.

F5SpecialBlanksOK

Enter an = sign in active cell.

Point to cell above and hit CTRL + ENTER.

You can leave the formulas as is or paste specialvalues.

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
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
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
__________________________

Fill the column with the last number
 

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.

F5SpecialBlanksOK

Enter an = sign in active cell.

Point to cell above and hit CTRL + ENTER.

You can leave the formulas as is or paste specialvalues.

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
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
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
__________________________

Fill the column with the last number
 

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
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.

F5SpecialBlanksOK

Enter an = sign in active cell.

Point to cell above and hit CTRL + ENTER.

You can leave the formulas as is or paste specialvalues.

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
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
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

Fill the column with the last number
 

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).Fo rmula = "=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
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
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
__________________________