Go Back   Tech Support Forum > Microsoft Support > Microsoft Office support

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 & 6 is


Closed Thread
 
Thread Tools Search this Thread
Old 05-15-2009, 11:04 AM   #1
Registered Member
 
texboy's Avatar
 
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.

__________________
texboy is offline  
Old 05-15-2009, 11:25 AM   #2
TSF Enthusiast
 
Elkar's Avatar
 
Join Date: Mar 2008
Location: Washington State, USA
Posts: 1,514
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

__________________
Elkar is offline  
Old 05-15-2009, 12:14 PM   #3
Registered Member
 
texboy's Avatar
 
Join Date: Nov 2003
Posts: 136
OS: WinXP MCE Pro



Quote:
Originally Posted by Elkar View Post
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?
__________________
texboy is offline  
Old 05-15-2009, 04:42 PM   #4
TSF Enthusiast
 
Elkar's Avatar
 
Join Date: Mar 2008
Location: Washington State, USA
Posts: 1,514
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 Sub
This, 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
__________________
Elkar is offline  
Old 05-18-2009, 08:13 AM   #5
Registered Member
 
texboy's Avatar
 
Join Date: Nov 2003
Posts: 136
OS: WinXP MCE Pro



I'll give it a try...

Thanks.
__________________
texboy is offline  
Old 05-20-2009, 09:13 AM   #6
Registered Member
 
texboy's Avatar
 
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.
__________________
texboy is offline  
Old 05-21-2009, 12:47 PM   #7
TSF Enthusiast
 
Elkar's Avatar
 
Join Date: Mar 2008
Location: Washington State, USA
Posts: 1,514
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
__________________
Elkar is offline  
Old 05-26-2009, 11:29 AM   #8
Registered Member
 
texboy's Avatar
 
Join Date: Nov 2003
Posts: 136
OS: WinXP MCE Pro



Quote:
Originally Posted by Elkar View Post
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.

Your code works great!

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

Thanks again.
__________________
texboy is offline  
Old 05-26-2009, 12:50 PM   #9
Registered Member
 
texboy's Avatar
 
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?
__________________
texboy is offline  
Old 05-26-2009, 03:33 PM   #10
TSF Enthusiast
 
Elkar's Avatar
 
Join Date: Mar 2008
Location: Washington State, USA
Posts: 1,514
OS: Win 7, Vista, XP SP3



Change the following two lines:
cnt = 5
Do While cnt <=15
These 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
__________________
Elkar is offline  
Old 05-26-2009, 04:11 PM   #11
Registered Member
 
texboy's Avatar
 
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.
__________________
texboy is offline  
Old 05-27-2009, 12:53 AM   #12
TSF Enthusiast
 
Elkar's Avatar
 
Join Date: Mar 2008
Location: Washington State, USA
Posts: 1,514
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 Sub
HTH
Elkar
__________________
Elkar is offline  
Old 05-27-2009, 01:53 PM   #13
Registered Member
 
texboy's Avatar
 
Join Date: Nov 2003
Posts: 136
OS: WinXP MCE Pro



It worked!


Thank you much.

__________________
texboy is offline  
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search

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 Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Post a Question


» Site Navigation
 > FAQ
  > 10.0.0.2


All times are GMT -7. The time now is 08:28 AM.


Copyright 2001 - 2014, Tech Support Forum

Windows 7 - Windows XP - Windows Vista - Trojan Removal - Spyware Removal - Virus Removal - Networking - Security - Top Web Hosts