Sorting Worksheet containing Tables and Regular Ranges

[UniMord]

I have a Table in columns A:K and a corresponding Table in columns Q:AC.
I'm trying to sort the entire range A:AC by columns G and A. The problem is, sorting appears to be disabled once a Table plus a regular range, or another Table, is included in the selection. This restriction is in both the GUI and VBA.

Can anyone recommend a workaround, short of converting the entire sheet to a single Table or converting the Tables to a regular range?

Thank you.

 

[Damon Ostrander]

Hi UniMord,

Why not simply move one of the tables to another worksheet?

Damon

 

[Jerry Sullivan]

Hi UniMord,

If I'm understanding your question, you want to be able to sort the two tables by Columns G and A as if they were one large table or range.

You could try the code below.
It temporarily adds a column to each table and then uses those columns to synch the sort.

Sub SortAdjacentTables()
    Dim cTbl1 As Range: Set cTbl1 = Range("A1")
    Dim cTbl2 As Range: Set cTbl2 = Range("Q1")
    Application.ScreenUpdating = False
    
    With cTbl1.ListObject
        .ListColumns.Add Position:=1
        With .ListColumns(1).Range
            .Formula = "=Row()"
            .Value = .Value
        End With
        With .Sort.SortFields
            .Clear
            .Add Key:=Range("G1").Offset(0, 1), SortOn:= _
                        xlSortOnValues, Order:=xlAscending
            .Add Key:=Range("A1").Offset(0, 1), SortOn:= _
                        xlSortOnValues, Order:=xlAscending
        End With
        .Sort.Header = xlYes
        .Sort.Apply
    End With
    With cTbl2.ListObject
        .ListColumns.Add Position:=1
        With .ListColumns(1).Range
            .Formula = "=MATCH(ROW()," & _
               cTbl1.ListObject.ListColumns(1).Range.Address & ",0)"
            .Value = .Value
        End With
        With .Sort.SortFields
            .Clear
            .Add Key:=cTbl2.Offset(0, -1), SortOn:= _
                        xlSortOnValues, Order:=xlAscending
        End With
        .Sort.Header = xlYes
        .Sort.Apply
        .ListColumns(1).Range.Delete
    End With
    cTbl1.ListObject.ListColumns(1).Range.Delete
End Sub

 

[UniMord]

Thanks Damon, but, moving one of the tables off the sheet won't help me, because I need both tables to sort simultaneously.

JS411, your approach solves the issue for me. Thank you.