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?

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

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

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

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.