Forum Discussion

Iron Contributor

Jun 08, 2022

Solved

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

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?
  - A_SIRAT
    Iron Contributor
    Jun 13, 2022
    It should be sorted first by Column D then E. Thereafter the sorting to be done again by Columns H then L. If I do a single sort from D to L, I do not get the desired results.
    thanks