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