Forum Discussion
BlueCollarVending
Dec 02, 2023Copper Contributor
Help with data sorting VBA
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 ...
djclements
Dec 02, 2023Silver Contributor
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!