Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 __________________________ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 __________________________ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 __________________________ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 __________________________ |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column picked randomly with probability relative to number of entr | Excel Worksheet Functions | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Running total w/2 columns - Excel | Excel Worksheet Functions |