View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Rick Rothstein \(MVP - VB\) Rick Rothstein \(MVP - VB\) is offline
external usenet poster
 
Posts: 2,202
Default Auto Numbers in cells

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