Help with data sorting VBA

BlueCollarVending 

To achieve sorting by quantity in your VBA code, you can add a sorting step after populating the data. Here is how you can modify your existing code:

Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    Dim invws As Worksheet
    Set invws = ThisWorkbook.Sheets("Inventory")
    nrows = WorksheetFunction.CountA(invws.Columns(1))

    ' Clear existing contents in the specified range
    Range("A7:I50000").ClearContents

    ' Populate data
    k = 7
    For i = 2 To nrows
        If invws.Range("E" & i) = 0 Or (invws.Range("I" & i) = "Yes" And invws.Range("A" & i) <> "") Then
            Range("A" & k) = invws.Range("A" & i)
            Range("B" & k) = invws.Range("B" & i)
            Range("C" & k) = invws.Range("C" & i)
            Range("D" & k) = invws.Range("D" & i)
            Range("E" & k) = invws.Range("E" & i)
            Range("F" & k) = invws.Range("F" & i)
            Range("G" & k) = invws.Range("G" & i)
            Range("H" & k) = invws.Range("H" & i)
            Range("I" & k) = invws.Range("I" & i)
            k = k + 1
        End If
    Next i

    ' Sort by Quantity (Column E)
    SortDataByColumnE

    Application.ScreenUpdating = True
End Sub

Sub SortDataByColumnE()
    ' Assuming the data starts from column A (A:I) and the header is in row 6
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("E7:E50000"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A6:I" & k - 1)
        .Header = xlYes
        .Apply
    End With
End Sub

VBA code is untested, it is recommended to back up the file to be on the safe side.

In this modification, a new subroutine SortDataByColumnE is added, which sorts the data based on the Quantity (Column E). This subroutine is called after populating the data.

Please adjust the range and header row according to your actual data setup if necessary.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.