CODEWITHSUNDEEP
excelvbasorting
Robin Trietsch
Robin Trietsch

Reputation: 1842

How to sort columns left to right?

I'm trying to sort the columns, based on the values shown in a certain row.

The data consists of two tables, separated by an empty line.

      A       B       C       D       E
1           12QA03  12QA01  12QA02
2   Step 1  32      23      82
3   Step 2  43      92      1
4   Step 3  12      12      84
5   Step 4  65      89      51
6   
7           24QA93  24QA91  24QA95  24QA98
8   Step 1  94      77      83      28
9   Step 2  92      30      5       19
10  Step 3  19      82      16      49
11  Step 4  11      41      7       17

The goal is to get this:

      A       B       C       D       E
1           12QA01  12QA02  12QA03
2   Step 1  23      82      32
3   Step 2  92      1       43
4   Step 3  12      84      12
5   Step 4  89      51      65
6           
7           24QA91  24QA93  24QA95  24QA98
8   Step 1  77      94      83      28
9   Step 2  30      92      5       19
10  Step 3  82      19      16      49
11  Step 4  41      11      7       17

I have written a macro that works properly for the top table, but gives an error for the second:

Sub SortData()

    'Sort top table
    With Range("B1:D5")
    .Rows.Sort Key1:=.Rows.Range("B1"), Order1:=xlAscending, _
    Orientation:=xlLeftToRight
    End With

    'Sort top table
    With Range("B7:E11")
    .Rows.Sort Key1:=.Rows.Range("B7"), Order1:=xlAscending, _
    Orientation:=xlLeftToRight
    End With

End Sub

What is going wrong? Or might there be a better method? Thanks in advance for your help!

Upvotes: 1

Views: 13857

Answers (3)

Robin Trietsch
Robin Trietsch

Reputation: 1842

The following code works.

Sub SortLTable()
    Range("B1:D5").Sort Key1:=Range("B1:D1"), Order1:=xlAscending, Orientation:=xlLeftToRight
    Range("B7:E11").Sort Key1:=Range("B7:E7"), Order1:=xlAscending, Orientation:=xlLeftToRight
End Sub

Upvotes: 1

pnuts
pnuts

Reputation: 59475

A little simpler perhaps: 

Sub SortLTable()
    Range("B7:E11").Sort Key1:=Range("B7:E7"), Order1:=xlAscending, Orientation:=xlLeftToRight
End Sub

Upvotes: 3

Andy G
Andy G

Reputation: 19367

Put something in A1 and A7 temporarily, such as "A", so that you have complete tables (with filled header columns). You can remove these values after sorting.

Your use of With and Rows is confusing the issue though:

Sub SortData()

    'Sort top table
    Range("A1,A7").Value = "A"
    Range("B1:D5").Sort Key1:=Range("B1"), Order1:=xlAscending, _
        Orientation:=xlLeftToRight

    'Sort top table
    Range("B7:E11").Sort Key1:=Range("B7"), Order1:=xlAscending, _
        Orientation:=xlLeftToRight
    Range("A1,A7").Value = ""
End Sub

Upvotes: 2

Related Questions