# Thread: Autonumbering? (2000 SR-1)

1. ## Autonumbering? (2000 SR-1)

I'm thinking of the way Access lets you add a new row, and each time you add a row, Access can autonumber it for you. I'd like to do something similar in Excel.
If I use the Fill feature, I have to know ahead of time the number of entries I'll have, and if I print it will print all of the cells up to the last filled cell, whether or not I've entered data in that row.
The only formulas I can think of using wouldn't actually assign a value to a cell, just a formula with relative values. So if I tried to sort by another column, the numbers wouldn't stay the same.
TIA!

2. ## Re: Autonumbering? (2000 SR-1)

You could do something like this in the worksheet's change event procedure:

<pre>Private Sub Worksheet_Change(ByVal Target As Range)
Dim strCkBlank As String
Dim lRow As Long, I As Long, lLastRow As Long
lRow = Target.Row
If Intersect(Target, Range("B1", "J65536")) Is Nothing Then
Exit Sub
End If
If Cells(lRow, 1).Value <> "" Then
Exit Sub
End If
For I = 2 To 10
strCkBlank = strCkBlank & Cells(lRow, I)
Next I
If strCkBlank = "" Then
Exit Sub
End If
lLastRow = Range("A65536").End(xlUp).Row
Application.EnableEvents = False
Cells(lRow, 1) = Application.WorksheetFunction.Max(Range("A1", Cells(lLastRow, 1))) + 1
Application.EnableEvents = True
End Sub
</pre>

3. ## Re: Autonumbering? (2000 SR-1)

I guess that one's a bit beyond me--I tried adding it but must not have done it correctly. It's too bad Excel doesn't ship with something like this!
Thanks for your help,
Angela

4. ## Re: Autonumbering? (2000 SR-1)

To add the code youe do the following:

1- Open the VBA Editor by pressing Alt+F11.

2- In the project explorer at the left of the VBE find your workbook. If there is a plus sign next to the project name (with your workbook name in parenthesis), click on it to expand it.

3- Find the line under your workbook that is labeled "Microsoft Excel Objects." If there is a plus sign next to it, click on the plus sign to expand it.

4- Find the worksheet object for the sheet you want to use this code for and double click on it. This should display the code window for that sheet on the right of the screen.

5- There should be two combo boxes at the top of the code window. The one on the left should display "General". Drop down this list and select "Worksheet" (probably the only other choice).

6- Drop down the right combo box and select the "Change" entry from the list. This should display Sub and End Sub lines for the Worksheet_Change event routine. Copy the code and paste it there.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•