[texboy]

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]

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]

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]

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]

I'll give it a try...

Thanks.

 

[texboy]

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]

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

 

[texboy]

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. :wave:

 

[texboy]

Opps, forgot to ask... how do I start numbering in, let's say on cell A5?

 

[Elkar]

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]

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]

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]

It worked!


Thank you much.