CODEWITHSUNDEEP
excelvbasorting
Leah
Leah

Reputation: 1

How to auto sort table

I am trying to auto sort three columns. I get the following error message when I open the file.

"we found a problem with some content in 'Blank.xlsm'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click yes."

When I click yes a pop-up appears saying:

"Excel was able to open the file by repairing or removing the unreadable content.
Remove Records: Sorting from /xl/worksheets/sheet2.xml part"

I am using the following code:

Sub SortMultipleColumns()
    With ActiveSheet.Sort
        .SortFields.Add Key:=Range("C6"), Order:=xlAscending
        .SortFields.Add Key:=Range("B6"), Order:=xlAscending
        .SortFields.Add Key:=Range("H6"), Order:=xlAscending
        .SetRange Range("B6:I40")
        .Header = xlYes
        .Apply
    End With
End Sub
    
Private Sub worksheet_change(ByVal Target As Range)
    Call SortMultipleColumns
End Sub

Upvotes: 0

Views: 132

Answers (1)

user14124037
user14124037

Reputation:

The code below show how to sort data in ascending order (if you created the table before).

    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim tbl As ListObject
    Set tbl = ws.ListObjects("Your_Table")
    Dim sortcolumn As Range
    Set sortcolumn = Range("Your_Table[Header_Name]")
    With tbl.Sort
       .SortFields.Clear
       .SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlAscending
       .Header = xlYes
       .Apply
    End With

In Header_Name write the header with which you want to sort the data.

To sort multiple columns, just add under the other one:

.SortFields.Add Key:=rRange2, SortOn:=xlSortOnValues, Order:=xlAscending

and replace rRange2 by your other column, and repeat the process for others.

Upvotes: 1

Related Questions