+ Reply to Thread
Results 1 to 2 of 2

how to auto-number rows that don't update when previous rows are removed

  1. #1
    Registered User
    Join Date
    11-23-2020
    Location
    Sacramento, CA
    MS-Off Ver
    Microsoft 365
    Posts
    7

    how to auto-number rows that don't update when previous rows are removed

    Hi, I am learning to utilize vba for our tracking log at work. Since multiple people will be updating this log, I wanted to utilize vba instead of formulas just in case it gets messed with.

    This log is a tracking log for all the requests that we receive. The main sheet has a code that will auto-fill column A with a unique tracking number after a command button is clicked. But I also have a Worksheet_change sub that will automatically remove the entire row if a completed date is entered in column T and it pastes into a different "Completed" sheet. When this happens, the tracking numbers for all rows after the "completed" row will update. I need the tracking number to not update when rows are removed because all rows inputted into the main sheet are copied into other sheets depending on what type of request it is. We also give the tracking number to the requestor.

    I've seen formulas using MAX to find the highest number in the column and then to increase it by one, but again, I want it to be automatic through vba as some of my coworkers do not know excel and want it to be as simple as possible. I don't' know if I'm just not searching with the right tags, but I can't seem to find anything that can help me. I know I could just already have the numbers pre-filled all the way down to row n but I want to try to keep it as clean as possible.

    The code I have now is:

    Sub FillSerialNumbers()
    With Range("A4:A" & Range("B" & Rows.Count).End(xlUp).Row)
    .Cells(1, 1).Value = 1
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Trend:=False
    End With
    End Sub

    Being new to macros, it's taken me a week to finally get the log working the way I want, and while it may look crude to the experienced (sorry..), this is the last bump that I can't overcome. Any and all help is appreciated. Thank you!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    11-23-2020
    Location
    Sacramento, CA
    MS-Off Ver
    Microsoft 365
    Posts
    7

    Re: how to auto-number rows that don't update when previous rows are removed

    I think I found something that was able to do what I needed. It is a Worksheet_Change, so I'll have to learn to reconcile the other Worksheet_Change that I have, but I am leaving it down below in case it helps someone else.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim maxNumber
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    ' don't run when more than one row is changed
    If Target.Rows.Count > 1 Then Exit Sub
    ' if column A in the current row has a value, don't run
    If Cells(Target.Row, 1) > 0 Then Exit Sub
    ' get the highest number in column A, then add 1 and write to the
    ' current row, column A
    maxNumber = Application.WorksheetFunction.Max(Range("A:A"))
    Target.Offset(0, -1) = maxNumber + 1
    End If
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 12-30-2019, 01:17 PM
  2. [SOLVED] Auto Fill X rows down depending on Number of rows in a table
    By MarkSe in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-24-2017, 02:20 AM
  3. [SOLVED] code to auto number rows - that will update when rows are added/deleted
    By dkitzerow in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-12-2015, 10:45 AM
  4. [SOLVED] Remove Rows based upon list of search terms, copy removed rows to new sheet
    By sam103329 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 02-19-2015, 08:47 PM
  5. Replies: 4
    Last Post: 03-05-2014, 02:40 AM
  6. auto update of values in rows when rows above is modified
    By sajithnair in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-02-2014, 01:45 AM
  7. Remove Rows based upon list of search terms, copy removed rows to new sheet
    By sam103329 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-26-2013, 10:32 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1