Forum Discussion

A_SIRAT's avatar
A_SIRAT
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...
Formulas and Functions
  • Doug_Robbins_Word_MVP's avatar
    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
A_SIRAT's avatar
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's avatar
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
  • A_SIRAT's avatar
    A_SIRAT
    Iron Contributor
    Jun 16, 2022
    Thanks again..
  • A_SIRAT's avatar
    A_SIRAT
    Iron Contributor
    Jun 15, 2022
    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
  • Doug_Robbins_Word_MVP's avatar
    Doug_Robbins_Word_MVP
    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
  • A_SIRAT's avatar
    A_SIRAT
    Iron Contributor
    Jun 14, 2022

    Doug_Robbins_Word_MVP 

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

    Thanks in advance.

     

     

Resources