Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto-numbering help (during inserting rows)
Hello all:
I have the following formula in every cell in my "A" column: =IF(NOT(ISBLANK(B1)),COUNTA(B$1:B1),"") This serves the purpose so that whenever I type something in column B, it will auto number the entries consecutively. When i delete a row, the numbering still works and all the numbers are reorganized but still consecutive. However when i insert a row, the formula does not exist in the new cell in column A.... and so my whole numbering gets thrown off.. Is there a way to make it such that there is auto numbering when i insert rows? Perhaps by having the formula above placed in the cell as soon as i insert a new row? Or something that makes sure each row in column A has a similar formula that allows this kind of auto-numbering? Please, Any help would be appreciated, Terrel Lobo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto-numbering help (during inserting rows)
right click on the sheet tab and select view code. Paste in code like this
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set rng = Range(Cells(1, 1), Cells( _ Rows.Count, 2).End(xlUp)(1, 0)) rng.Formula = "=IF(NOT(ISBLANK(B1)),COUNTA(B$1:B1),"""")" End Sub This will probably affect your ability to paste on that worksheet. You have to look at the trade off of convenience vice functionality. -- Regards, Tom Ogilvy "LoboNetwork" wrote: Hello all: I have the following formula in every cell in my "A" column: =IF(NOT(ISBLANK(B1)),COUNTA(B$1:B1),"") This serves the purpose so that whenever I type something in column B, it will auto number the entries consecutively. When i delete a row, the numbering still works and all the numbers are reorganized but still consecutive. However when i insert a row, the formula does not exist in the new cell in column A.... and so my whole numbering gets thrown off.. Is there a way to make it such that there is auto numbering when i insert rows? Perhaps by having the formula above placed in the cell as soon as i insert a new row? Or something that makes sure each row in column A has a similar formula that allows this kind of auto-numbering? Please, Any help would be appreciated, Terrel Lobo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto-numbering help (during inserting rows)
Thanks Tom,
That is exactly what I wanted per my specifications... i found some additional problems that came up in some existing spreadsheets but thats my fault hehe. "Tom Ogilvy" wrote: right click on the sheet tab and select view code. Paste in code like this Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set rng = Range(Cells(1, 1), Cells( _ Rows.Count, 2).End(xlUp)(1, 0)) rng.Formula = "=IF(NOT(ISBLANK(B1)),COUNTA(B$1:B1),"""")" End Sub This will probably affect your ability to paste on that worksheet. You have to look at the trade off of convenience vice functionality. -- Regards, Tom Ogilvy "LoboNetwork" wrote: Hello all: I have the following formula in every cell in my "A" column: =IF(NOT(ISBLANK(B1)),COUNTA(B$1:B1),"") This serves the purpose so that whenever I type something in column B, it will auto number the entries consecutively. When i delete a row, the numbering still works and all the numbers are reorganized but still consecutive. However when i insert a row, the formula does not exist in the new cell in column A.... and so my whole numbering gets thrown off.. Is there a way to make it such that there is auto numbering when i insert rows? Perhaps by having the formula above placed in the cell as soon as i insert a new row? Or something that makes sure each row in column A has a similar formula that allows this kind of auto-numbering? Please, Any help would be appreciated, Terrel Lobo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto fill formulae when inserting rows | Excel Discussion (Misc queries) | |||
Auto numbering inserted rows. | Excel Discussion (Misc queries) | |||
Macro auto inserting rows and formulas | Excel Discussion (Misc queries) | |||
Auto numbering | Excel Worksheet Functions | |||
Auto-numbering | Excel Worksheet Functions |