auto-numbering help (during inserting rows)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LoboNetwork - 21 Mar 2006 13:49 GMT

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 to this Message

Tom Ogilvy - 21 Mar 2006 14:27 GMT

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.

Signature

Regards,
Tom Ogilvy

> Hello all:
>
[quoted text clipped - 16 lines]
>
> Terrel Lobo

Reply to this Message

LoboNetwork - 21 Mar 2006 14:59 GMT

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.

> right click on the sheet tab and select view code. Paste in code like this
>
[quoted text clipped - 27 lines]
> >
> > Terrel Lobo

Reply to this Message