Automate Custom Sorting with VBA

[A_SIRAT]

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)

[#2]

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

[#3]

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)

[#4]

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

[#5]

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

[#6]

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.

[#7]

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

[#8]

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

[#9]

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

[#10]

@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

[#11]

@Doug_Robbins_Word_MVP 

It does the custom sorting but gives this error afterwards. Please check and advise.

Thanks in advance.

[#12]

What was the error message?

[#13]

@Doug_Robbins_Word_MVP 

See below screenshot.

[#14 · Accepted solution]

@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

[#15]

Thanks.. this seems to be working but I am trying to figure out why the sorting is NOT as per my expectation.
Why do you have sorting on Range B1, then D1,E1 then A1 ? I am trying to re-arrange the order hoping to get it right. Thanks to advise

[#16]

Thanks again..