Tech Support Forum Auto-Numbering in Excel after Row insert
 Site Map Posting Help Register Rules Search Today's Posts Mark Forums Read

# Auto-Numbering in Excel after Row insert

This is a discussion on Auto-Numbering in Excel after Row insert within the Microsoft Office support forums, part of the Tech Support Forum category. Say I have rows 1-10 (numbered) in column A, I then insert a row, say between 5 &amp; 6 is

 05-15-2009, 11:04 AM #1 Registered Member     Join Date: Nov 2003 Posts: 136 OS: WinXP MCE Pro Say I have rows 1-10 (numbered) in column A, I then insert a row, say between 5 & 6 is it possible I can set them to auto re-number automatically? Thanks.

 05-15-2009, 11:25 AM #2 TSF Enthusiast     Join Date: Mar 2008 Location: Washington State, USA Posts: 1,515 OS: Win 7, Vista, XP SP3 Instead of entering the numbers in Column A, you could just use this formula in all of your cells: =ROW() This will insert the corresponding row number into the cell and will automatically adjust if rows are inserted/deleted. You can add or subtract from that formula as well if your numbers don't match up with the actual row. For example: =ROW()-1 or =ROW()+12 HTH Elkar
05-15-2009, 12:14 PM   #3
Registered Member

Join Date: Nov 2003
Posts: 136
OS: WinXP MCE Pro

Quote:
 Originally Posted by Elkar Instead of entering the numbers in Column A, you could just use this formula in all of your cells: =ROW() This will insert the corresponding row number into the cell and will automatically adjust if rows are inserted/deleted. You can add or subtract from that formula as well if your numbers don't match up with the actual row. For example: =ROW()-1 or =ROW()+12 HTH Elkar

Thanks, I had already tried this... When I insert a new row, I have to manually do something to put the missing number on the newly added row. The above also adds and extra number (e.g., 11) to the bottom of the numbering which I don't need.

Other suggestions?

 05-15-2009, 04:42 PM #4 TSF Enthusiast     Join Date: Mar 2008 Location: Washington State, USA Posts: 1,515 OS: Win 7, Vista, XP SP3 You're going to need a macro for this then. Perhaps something like: Private Sub Worksheet_Change(ByVal Target As Range) Dim cnt As Integer cnt = 1 Application.EnableEvents = False Do While cnt <= 10 Range("A" & cnt).Value = cnt cnt = cnt + 1 Loop Range("A11").Value = "" Application.EnableEvents = True End SubThis, will keep the numbers in cells A1 thru A10 as 1 thru 10. It will also prevent the value 10 from moving down to cell A11 if a single row or cell is inserted above. Depending on your needs, you may need to tweak the code a bit. HTH Elkar
 05-18-2009, 08:13 AM #5 Registered Member     Join Date: Nov 2003 Posts: 136 OS: WinXP MCE Pro I'll give it a try... Thanks.
 05-20-2009, 09:13 AM #6 Registered Member     Join Date: Nov 2003 Posts: 136 OS: WinXP MCE Pro Hi Elkar, I just had a chance to give it a try. However, I'm a little lost with the macro... Would you mind elaborating (steps) on how to get it to work. I'm not that great with Excel. Thanks.
 05-21-2009, 12:47 PM #7 TSF Enthusiast     Join Date: Mar 2008 Location: Washington State, USA Posts: 1,515 OS: Win 7, Vista, XP SP3 Open up your workbook. Hit Alt-F11 to bring up the Visual Basic Editor. On the left side you should see a "Project - VBAProject" window. Find your workbook name there, and expand the tree until you see your individual sheet names listed. Double-click on the worksheet you want to apply this to (ex. "Sheet1" (YourSheetName)) You should now see a larger window open to the right Paste the above code into that window. Close the Visual Basic Editor Try it out HTH Elkar
05-26-2009, 11:29 AM   #8
Registered Member

Join Date: Nov 2003
Posts: 136
OS: WinXP MCE Pro

Quote:
 Originally Posted by Elkar Open up your workbook. Hit Alt-F11 to bring up the Visual Basic Editor. On the left side you should see a "Project - VBAProject" window. Find your workbook name there, and expand the tree until you see your individual sheet names listed. Double-click on the worksheet you want to apply this to (ex. "Sheet1" (YourSheetName)) You should now see a larger window open to the right Paste the above code into that window. Close the Visual Basic Editor Try it out HTH Elkar
First, sorry for the long reply... I had a long Holiday weekend.

I already tested it and works fine... I'll keep playing around and see if my boss likes it.

Thanks again.

 05-26-2009, 12:50 PM #9 Registered Member     Join Date: Nov 2003 Posts: 136 OS: WinXP MCE Pro Opps, forgot to ask... how do I start numbering in, let's say on cell A5?
 05-26-2009, 03:33 PM #10 TSF Enthusiast     Join Date: Mar 2008 Location: Washington State, USA Posts: 1,515 OS: Win 7, Vista, XP SP3 Change the following two lines: cnt = 5 Do While cnt <=15These two numbers are basically your starting row and ending row. Also, you'd likely need to change this line as well: Range("A16").Value = ""The "A16" should be the row following the last cell in your range. And now that I think about it, you could probably rewrite it as: Range("A" & cnt + 1).Value = ""That way it will always be your last row + 1. HTH Elkar
 05-26-2009, 04:11 PM #11 Registered Member     Join Date: Nov 2003 Posts: 136 OS: WinXP MCE Pro This starts numbering at #5, I meant to start in cell A5 from #1. Just in case we need to add something on rows 1 & 2 prior to starting numbering.
 05-27-2009, 12:53 AM #12 TSF Enthusiast     Join Date: Mar 2008 Location: Washington State, USA Posts: 1,515 OS: Win 7, Vista, XP SP3 Ah yes, didn't think of that. How about we just rewrite the thing to be easier to customize. Try this: Private Sub Worksheet_Change(ByVal Target As Range) Dim StartNum As Integer Dim FirstCell As Integer Dim LastCell As Integer StartNum = 1 FirstCell = 5 LastCell = 15 Application.EnableEvents = False Do While FirstCell <= LastCell Range("A" & FirstCell).Value = StartNum FirstCell = FirstCell + 1 StartNum = StartNum + 1 Loop Range("A" & LastCell + 1).Value = "" Application.EnableEvents = True End SubHTH Elkar
 05-27-2009, 01:53 PM #13 Registered Member     Join Date: Nov 2003 Posts: 136 OS: WinXP MCE Pro It worked! Thank you much.

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is on Smilies are on [IMG] code is on HTML code is OffTrackbacks are Off Pingbacks are Off Refbacks are Off Forum Rules

 » Recent Discussions [SOLVED] New laptop charger