Status
Not open for further replies.
1 - 13 of 13 Posts

#### texboy

· Registered
Joined
·
136 Posts
Discussion Starter · ·
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?:4-dontkno

Thanks.

#### Elkar

· Registered
Joined
·
1,516 Posts
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

#### texboy

· Registered
Joined
·
136 Posts
Discussion Starter · ·
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?

#### Elkar

· Registered
Joined
·
1,516 Posts
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

#### texboy

· Registered
Joined
·
136 Posts
Discussion Starter · ·
I'll give it a try...

Thanks.

#### texboy

· Registered
Joined
·
136 Posts
Discussion Starter · ·
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.

#### Elkar

· Registered
Joined
·
1,516 Posts
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

#### texboy

· Registered
Joined
·
136 Posts
Discussion Starter · ·
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. :wave:

#### texboy

· Registered
Joined
·
136 Posts
Discussion Starter · ·
Opps, forgot to ask... how do I start numbering in, let's say on cell A5?

#### Elkar

· Registered
Joined
·
1,516 Posts
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

#### texboy

· Registered
Joined
·
136 Posts
Discussion Starter · ·
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.

#### Elkar

· Registered
Joined
·
1,516 Posts
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

#### texboy

· Registered
Joined
·
136 Posts
Discussion Starter · ·
It worked!

Thank you much.

1 - 13 of 13 Posts
Status
Not open for further replies.