Sorting a table with multiple columns

[dpalomino1009]

Hello,

I'm trying to sort 3 columns in a table that goes from A2-G192, but the code I came up with is not working. Not sure where I went wrong. Posting the code below, please let me know.

Thank you,

Private Sub Worksheet_Change(ByVal Target As Range)On Error Resume Next
Application.ScreenUpdating = False
If Not Intersect(Target, Range("E2:E192")) Is Nothing Then
      With Sheets("User iMac & PC").ListObjects("Table1").Sort
    .SortFields.Clear
    .SortFields.Add Key1:=Range("Table1[Names]"), SortOn:=xlSortOnCellColor, Order:=xlAscending
    .SortFields.Add Key2:=Range("Table1[A-Z]"), SortOn:=xlSortOnValues, Order:=xlAscending
    .SortFields.Add Key3:=Range("Table1 [Names]"), SortOn:=xlSortOnValues, Order:=xlAscending
    .SetRange Range("Table1")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End If
Application.ScreenUpdating = True
End Sub

 

[igold]

Does this work for you. You do realize that you have the column "Names" sorted twice...

Private Sub Worksheet_Change(ByVal Target As Range)


    On Error Resume Next
    Application.ScreenUpdating = False
    If Not Intersect(Target, Range("E2:E192")) Is Nothing Then
        With Sheets("User iMac & PC").ListObjects("Table1").Sort
            .SortFields.Clear
            .SortFields.Add key:=Range("Table1[Names]"), SortOn:=xlSortOnCellColor, Order:=xlAscending, _
                DataOption:=xlSortNormal
            .SortFields.Add key:=Range("Table1[A-Z]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                DataOption:=xlSortNormal
            .SortFields.Add key:=Range("Table1 [Names]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                DataOption:=xlSortNormal
        End With
        With ActiveWorkbook.Worksheets("Tables").ListObjects("Table1").Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If
    Application.ScreenUpdating = True


End Sub

 

[dpalomino1009]

I tried it, but it didn't work. I wanted to sort the Names column by color first, then the A-Z column alphabetically, and after the Names column alphabetically, in that order, but it seems only account for key 3 and ignore key 1 and 2.

 

[dpalomino1009]

I found something, seems like .Add2 is for tables, and that worked! I been coming back to this on and off today. Trimmed it some more and noting the edited code bellow.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next


Application.ScreenUpdating = False


If Not Intersect(Target, Range("E2:E192")) Is Nothing Then


    With ActiveWorkbook.Worksheets("User iMac & PC").ListObjects("Table1").Sort
        With .SortFields
            .Clear
            .Add2 Key:=Range("Table1[Names]"), SortOn:=xlSortOnCellColor, Order:=xlAscending
            .Add2 Key:=Range("Table1[A-Z]"), SortOn:=xlSortOnValues, Order:=xlAscending
            .Add2 Key:=Range("Table1[Names]"), SortOn:=xlSortOnValues, Order:=xlAscending
        End With
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
    
End If


Application.ScreenUpdating = True


End Sub

 

[igold]

I am glad you got it working. Thanks for posting back about the .Add2 for tables...