Sort Two Columns at a time

**joe_ll** · 12-01-2010, 10:30 PM

Hi all,

I'm pretty new to macros and whilst I've had some wins so far, I've hit a brick wall and was hoping someone could translate my logic into a working macro or at least point me in the right direction.

I have a sheet with several columns, grouped in pairs. What I want to do is sort each pair by the values in the second column.

The columns look something like this:

| A | B | C | D |
| Item1 | Val_1 | Item2 | Val_2 |
| foo | 26 | foo | 3 |
| bar | 12 | bar | 9 |

The pseudocode I've come up with looks like this:

-----
columnOne = A
columnTwo = B

WHILE there's still columns with values in them

sort (columnOne:columnTwo) by columnTwo descending with headers
columnOne+= 2 // i.e. offset 2 columns to the right C, E, H
columnTwo+= 2 // i.e. D, F, G

REPEAT
-----

I would greatly appreciate any assistance with this.

Thanks in advance,
Joe

**Paul** · 12-02-2010, 01:04 AM

Hi Joe, welcome to the forum.

Perhaps the following macro will help you out..

Sub sortCols()
Dim cols As Long, LR As Long, i as Long

Application.ScreenUpdating = False

' Get last column
cols = Range("A1").End(xlToRight).Column

' Check to see if there is an odd # of columns.
' If so, don't include the last column.
If cols Mod 2 = 1 Then cols = cols - 1

' Loop through the used columns
For i = 1 To cols Step 2

    ' Get last row for each set of two columns
    ' Based on the last row of the first column in the set
    LR = Cells(Rows.Count, i).End(xlUp).Row
    
    ' Sort by the second column (i + 1) in each set
    With Worksheets("Sheet1").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range(Cells(2, i + 1), Cells(LR, i + 1)), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range(Cells(1, i), Cells(LR, i + 1))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Next i
Application.ScreenUpdating = True
End Sub