Help with data sorting VBA

[BlueCollarVending]

I need help with data sorting vba. I have a "Reports" tab that automatically generates data from other tabs to show low qty items. But at the moment the info is sorting by default (AZ, column A) but i would like it to sort by quantity instead. Here is the vba i currently have.

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Dim invws As Worksheet
Set invws = ThisWorkbook.Sheets("Inventory")
nrows = WorksheetFunction.CountA(invws.Columns(1))
'nrows = invws.Cells(invws.Rows.Count, 1).End(xlUp).Row

Range("A7:I50000").ClearContents
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

Application.ScreenUpdating = True
End Sub
    
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Calculate
End Sub

[#2]

@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.

[#3]

When i plugged in the code, it said there was a error and wouldnt work. it says " application-defined or object-defined error"

[#4]

@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!