Automate Custom Sorting with VBA

Hi,

Please help me automate the custom sorting for the attached file.

I need a VBA button to do all these (columns A: N), in this Format; The data could be 10 rows this week or even 2000 rows depending on the orders.

1. Delete orders lines that have zero quantities in column F.

2. Delete all order lines EXCEPT for ABCD LIMITED.

2. Custom Sort First by Column D then by E (Order A to Z).

3.The Custom sort again all data by Columns H then by L (Order A to Z)

Raw Orders Sorting.xlsx12 KB

Formulas and Functions

Doug_Robbins_Word_MVP

Jun 15, 2022

A_SIRAT It turns out that using Visual Basic, a maximum of 3 levels can be sorted. Therefore, it will be necessary to use


Dim i As Long
With Sheets("Raw Orders").Range("A1")
    For i = .CurrentRegion.Rows.Count - 1 To 1 Step -1
        If .Offset(i, 5) = 0 Then
            .Rows(i + 1).EntireRow.Delete
        End If
    Next i
    For i = .CurrentRegion.Rows.Count - 1 To 1 Step -1
        If .Offset(i, 6) <> "ABCD LIMITED" Then
            .Rows(i + 1).EntireRow.Delete
        End If
    Next i
    .CurrentRegion.Sort Key1:=Range("B1"), Order1:=xlAscending, _
                        Header:=xlYes
    .CurrentRegion.Sort Key1:=Range("D1"), Order1:=xlAscending, _
                        Key2:=Range("E1"), Order2:=xlAscending, _
                        Key3:=Range("A1"), Order3:=xlAscending, _
                        Header:=xlYes
End With

Doug_Robbins_Word_MVP
MVP
Jun 08, 2022
A_SIRAT A macro containing the following code will delete the unwanted rows

Dim i As Long With Sheets("Raw Orders").Range("A1") For i = .CurrentRegion.Rows.Count - 1 To 1 Step -1 If .Offset(i, 5) = 0 Then .Rows(i + 1).EntireRow.Delete End If Next i For i = .CurrentRegion.Rows.Count - 1 To 1 Step -1 If .Offset(i, 6) <> "ABCD LIMITED" Then .Rows(i + 1).EntireRow.Delete End If Next i End With

I am not sure of the two sorts that you want. Is something to be done with the data following the first sort and before the second is performed?
- A_SIRAT
  Iron Contributor
  Jun 09, 2022
  Thank you Doug for the code on the unwanted rows.
  As for the sorting, it needs to be done in that order. i.e.
  
  1. Custom Sort First by Column D then by E (Order A to Z).
  2.The Custom sort again all data by Columns H then by L (Order A to Z)
  - Doug_Robbins_Word_MVP
    MVP
    Jun 09, 2022
    A_SIRAT Is anything to be done between sorts 1 and 2?
    Or, is it just a single sort First by Column D, then by E and then by H and then by L?

Forum Discussion

Automate Custom Sorting with VBA

Share

Resources