Autonumber in excel???

[Wray69]

I have a table where column A has numbers 1-100. If I insert a row somewhere in the middle it makes a break in the numbers.

So my question is, Is there a way for when I insert that row for it to number it correctly?

Thank You,
Wray

 

[VulcanJedi]

type in the cells and autofilter down(this will reference the row number, so you should always be set (excpet you will need to redo the autofill if you insert rows):

=row()

hope this helps Tranpkp

 

[Wray69]

Thanks for the quick reply, but I am looking for a way where I wouldnt have to do the autofill...

Regards,
Wray

 

[mbarron]

How do you have it numbering the column? Do you have it as a filled series, or is A2=A1+1, A3=A2+1 etc? Are there any empty cells (before you insert of course)?

If you are using the A2=A1+1 method and there are no gaps, try this:

After you insert the row, type something (doesn't matter what it is) in the cell for the numbering. Goto cell A2 (or even the cell above the inserted row) Move your mouse over the drag handle in the lower right corner of the cell. Your cursor will turn intro a "+". Double click. Excel will then copy the formula down the column until it comes to a gap between columns.

Mike

 

[mbarron]

Slight correction; it will stop when it come to a gap betweens rows. (or a gap in the column) Mike

 

[Wray69]

Thanks for the advice so far, have been doing these things in the past. I am really looking for a way that it will just autonumber the column when a row is inserted.

Regards,
Wray

 

[darag2358]

Probably needs to be done in VB. I'm a little rusty but something like this if you are working with range A1:A100
=======================
Sub Autonumber()
Range("A:A&quot.Delete
For c = 1 To 100
Cells(c, 1).Value = c
Next c
End Sub
=======================
Then you can assign it a ctrl+letter to run, or a button. Maybe you could also link it to an event, like have it run whenever there is a change to the worksheet or something. Again a little rusty...

 

[darag2358]

Ooooops. Change 2nd line to:

Range("A:A&quot.Clear

Did I mention I was rusty?