Help with data sorting VBA

BlueCollarVending For basic sorting, you can use the Range.Sort method. On line 24 of your code, between "Next i" and "Application.ScreenUpdating = True", insert the following code:

    If k > 8 Then
        Dim rg As Range
        Set rg = Range("A7:I" & k - 1)
        rg.Sort key1:=rg.Columns(5), order1:=xlAscending
    End If

Just change the column number (5) to whatever number the "Quantity" column is in your worksheet. Also, if you prefer to sort from largest to smallest, change the order to xlDescending.

On a side note, the loop can also be simplified by using invws.Cells, rather than invws.Range, then looping through the columns from 1 to 9 (A to I), as follows:

    Dim i As Long, j As Long, k As Long
    k = 7
    For i = 2 To nrows
        If invws.Cells(i, 5) = 0 Or invws.Cells(i, 9) = "Yes" And invws.Cells(i, 1) <> "" Then
            For j = 1 To 9
                Cells(k, j) = invws.Cells(i, j)
            Next j
            k = k + 1
        End If
    Next i

I hope that helps. Cheers!

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.