Sorting a table with multiple columns

dpalomino1009

New Member
Joined
Mar 3, 2019
Messages
20
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,

Code:
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,506
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Does this work for you. You do realize that you have the column "Names" sorted twice...

Code:
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

New Member
Joined
Mar 3, 2019
Messages
20
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

New Member
Joined
Mar 3, 2019
Messages
20
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.

Code:
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

Well-known Member
Joined
Jul 8, 2014
Messages
2,506
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I am glad you got it working. Thanks for posting back about the .Add2 for tables...
 

Watch MrExcel Video

Forum statistics

Threads
1,109,429
Messages
5,528,705
Members
409,830
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top