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?

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

"Nathan Elphick" wrote in message
...
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?

Dear Paul,

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

"Paul B" wrote:

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

"Nathan Elphick" wrote in message
...
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?

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

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)" wrote:

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

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

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

"Rick Rothstein (MVP - VB)" wrote:

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

It works on my end, so I am not sure what to say, except perhaps to start
over with a new work sheet. You are placing this formula...

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

in cell A4 and then copying it down to cell A48. You are then going to
right-click that sheets tab and select View Code from its popup menu and
then copy/paste this....

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

into the code window that appeared in the VBA editor. It is important that
the above function be placed in the code window for the sheet where the
auto-numbering is to take place. I just tried it out again to be sure and
performing the above steps produces a working set-up that does what you said
you were after. I have attached an XL2003 worksheet to this posting that has
everything in place so that (if you have XL2003 or XL2007), you can see how
it works.

Rick


"Nathan Elphick" wrote in message
...
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

"Rick Rothstein (MVP - VB)" wrote:

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