Auto Numbers in cells

[Guest]

I want to set up a template with numbers, say 1 - 10 in alternate cells, say
A1, A3, A5 - A19 so that if you add/or delete a row the numbered cell is
added or deleted automatically thus the numbers always remain in numerical
order. Can this be done?

 

[Paul B]

Nathan, in your cells try putting =Row() and see if that will work for you

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

Dear Paul,

Great but every other row is blank and I want the numbers to remain in
numerical order. Thank you.

 

[Rick Rothstein \(MVP - VB\)]

I want to set up a template with numbers, say 1 - 10 in alternate cells,

say
A1, A3, A5 - A19 so that if you add/or delete a row the numbered cell is
added or deleted automatically thus the numbers always remain in numerical
order.

I'm guessing from the above description that you want 1 in A1, 2 in A3, 3 in
A5, etc.; and that you want the alternating row numbers to always be 1 in
A1, 2 in A3, 3 in A5, etc. even after rows are inserted or deleted.

Can this be done?

You could do this with formulas only if you only had to handle deletions;
but, since you have to handle insertions too, you will need to use a macro.
First, we need to set up your spreadsheet by putting this formula

=IF(AND(ROWS($1:1)<20,MOD(ROWS($1:1),2)=1),(1+ROWS($1:1))/2,"")

in A1 and copying down to A19. That will put the numbers you requested into
the cells you indicated. Now, add this macro code to the worksheet's VBA
code window (right click the worksheet's tab, select View Code from the
popup menu and Copy/Paste the following into the code window that
appears)...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range
If Intersect(Target, Range("A1:A20")) Is Nothing Then Exit Sub
For Each C In Range("A1:A20")
If Not C.HasFormula Then
C.Formula = "=IF(AND(ROWS($1:" & C.Row & ")<20,MOD(ROWS($1:" & _
C.Row & "),2)=1),(1+ROWS($1:" & C.Row & "))/2,"""")"
End If
Next
End Sub


Rick

 

[Guest]

Rick,

Brilliant - may thanks.

I have changed the spreadsheet so that the range is 1 to 23, with 1 starting
in Cell A4 and 23 ending in Cell A48 - How does this effect the code?

Thanks again,

Nathan

 

[Rick Rothstein \(MVP - VB\)]

Brilliant - may thanks.

I don't know about "brilliant", but you are welcome.

I have changed the spreadsheet so that the range is 1 to 23, with 1
starting
in Cell A4 and 23 ending in Cell A48 - How does this effect the code?

Yes, it affects the code and what is put in the spreadsheet initially (the
code and formulas are location-sensitive). For your latest requirement, put
this in A4 (note that I said A4, not A1)...

=IF(AND(ROWS($1:4)<49,MOD(ROWS($1:4),2)=0),(ROWS($1:4)-2)/2,"")

and copy it down to A48. Replace the function (in the worksheet's code
window) that I gave you earlier with this new function...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range
If Intersect(Target, Range("A4:A48")) Is Nothing Then Exit Sub
For Each C In Range("A4:A48")
If Not C.HasFormula Then
C.Formula = "=IF(AND(ROWS($1:" & C.Row & ")<49,MOD(ROWS($1:" & _
CStr(C.Row) & "),2)=0),(ROWS($1:" & CStr(C.Row) &
")-2)/2,"""")"
End If
Next
End Sub

Both of the above should do what you have now asked for. Because the
function is in the Worksheet Change event, you will have to make the above
changes **before** you attempt to erase the formulas you currently have in
A1:A3 (otherwise the old function will just refill them in for you).

Rick

 

[Guest]

Rick,

Thank you - you're too modest!!

I have done as instructed but am getting a Compile Error / Syntax Error when
inserting and deleting lines - any ideas?

Nathan