Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Legare Coleman

Posting Permissions

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