Forum Discussion

Iron Contributor

Jun 07, 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 14, 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 12, 2022

Sorting by Column D, then by E and then by H and then by L should give the same results as doing in two steps.

A_SIRAT

Iron Contributor

Jun 14, 2022

Doug_Robbins_Word_MVP

I do not get the same results when doing the sorting in two steps. I have attached an example file of how I would like to have it. Hope this is clear.

CUSTOM SORT SAMPLE...xlsx43 KB

Doug_Robbins_Word_MVP

MVP

Jun 14, 2022

A_SIRAT A single sort of the data as shown below produces the same result as your two step sort

A_SIRAT

Iron Contributor

Jun 14, 2022

Thanks Doug. Understood :). Please now share the VBA code because I have to do this every now and then. The rows could reach 3000.

Doug_Robbins_Word_MVP

MVP

Jun 14, 2022

A_SIRAT For the column layout in the workbook that you provided, the complete code would be

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("D1"), Order1:=xlAscending, _
                        Key2:=Range("E1"), Order2:=xlAscending, _
                        Key3:=Range("A1"), Order3:=xlAscending, _
                        Key4:=Range("B1"), Order4:=xlAscending, _
                        Header:=xlYes
End With