Need help sorting values across columns

[harrysolomon]

I have an Excel worksheet which contains an ISO Country Code value in a cell and then the next cell to the right contains the Country name corresponding to that ISO Country code.

For example, cell A1 contains the value "US" and cell B1 contains the name "United States”, cell C1 contains "CH" and cell D1 contains "Switzerland", cell E1 contains “DE” and cell F1 contains “Germany” .

This country code & description information exists across a single row and the number of cells on a row which contain a value will vary.

The information listed across the row is currently sorted in alphabetical order on the ISO Country Code value and I want to re-sort this information alphabetically within the row by the ISO Code description using VBA.

So using the previous example data, if the original order of the data in the row is
cell A1 = "US" & cell B1 = "United States”
cell C1 = “CH” & cell D1 = “Switzerland”
cell E1 = "DE" & cell F1 = "Germany"

After sorting, the desired end result should be
cell A1 = "DE" & cell B1 = "Germany"
cell C1 = “CH” & cell D1 = “Switzerland”
cell E1 = "US" & cell F1 = "United States”


Thanks in advance for any help.

 

[JoeMo]

Untested - try on a copy of your sheet:

Sub SortByCodeName()
Dim lC As Long, R As Range, vA As Variant, i As Long, j As Long
lC = Cells(1, Columns.Count).End(xlToLeft).Column
Set R = Range("A1", Cells(1, lC))
vA = R.Value
For i = LBound(vA, 2) + 1 To UBound(vA, 2) - 2 Step 2
    For j = i + 2 To UBound(vA, 2) Step 2
        If vA(1, i) > vA(1, j) Then
            temp1 = vA(1, i)
            vA(1, i) = vA(1, j)
            vA(1, j) = temp1
            temp2 = vA(1, i - 1)
            vA(1, i - 1) = vA(1, j - 1)
            vA(1, j - 1) = temp2
        End If
    Next j
Next i
R.Value = vA
End Sub

 

[harrysolomon]

Fantastic, it works perfectly. You are indeed a MrExcel MVP.
Thanks so much.

 

[JoeMo]

Fantastic, it works perfectly. You are indeed a MrExcel MVP.
Thanks so much.

You are welcome - thanks for the reply.