I have two different columns on Excel. I want to create a script that will sort Column F, (Have a header) and will sort Column B based on where Column F's sort worked out. (Without affecting any other columns however!)

So if I have

    ColB       ColF
 1. Cat        2
 2. Mouse      1
 3. Dog        3

The sort will give me

    ColB       ColF
 1. Mouse        1
 2. Cat          2
 3. Dog          3

How can I do this? I tried recording a macro, (or just plain up trying to sorting it with the two columns clicked and the sort button), but I get an error saying "The command cannot be performed with multiple selections, Click a single range and try again"

  • 4
  • Had a friend tell me this was a better spot to ask the question, so I did :/ – Geoff Jul 20 '12 at 1:43
  • 3
    I think there aren't any low-effort, non-VBA solutions forthcoming, so maybe it would help if you start a step farther back and describe why you're trying to do this. There may be a better way of going forward that doesn't require sorting in this way. – Excellll Jul 20 '12 at 19:10

I suspect there must be a better way to accomplish whatever it is you want to do by sorting these columns, but here is a VBA solution that will do exactly what you asked for. Beware this code assumes there are no blank cells in the ranges you want to sort. Please leave a comment if this is a problem, because it will be pretty easy to fix.

Sub nonadjacentsort()
Dim rng1 As Range, rng2 As Range, rngTmp As Range, s1 As Worksheet, tmpS As Worksheet
Dim tmpArr1() As Variant, tmpArr2() As Variant
Dim i As Long
Set s1 = ActiveSheet
'Set Ranges to sort.  This assumes there are no blanks in your data.
Set rng1 = s1.Range("B1", Range("B1").End(xlDown))
Set rng2 = s1.Range("F1", Range("F1").End(xlDown))
'Load first column into temporary array
tmpArr1 = rng1.Value
'Load data into larger array that will hold both columns
ReDim tmpArr2(1 To UBound(tmpArr1, 1), 1 To 2) As Variant
For i = 1 To UBound(tmpArr1, 1)
    tmpArr2(i, 1) = tmpArr1(i, 1)
Next i
'Load second column into temporary array
Erase tmpArr1
tmpArr1 = rng2.Value
'Load second column into larger array
For i = 1 To UBound(tmpArr1, 1)
    tmpArr2(i, 2) = tmpArr1(i, 1)
Next i
Erase tmpArr1
'Add new sheet and print two columns there together.
Application.ScreenUpdating = False
Set tmpS = Sheets.Add
Set rngTmp = tmpS.Range("A1").Resize(UBound(tmpArr2, 1), 2)
rngTmp = tmpArr2
Erase tmpArr2
'Sort by second column (Column F of original data)
rngTmp.Sort rngTmp.Cells(1, 2), xlAscending, Header:=xlYes
'Load sorted data into array and then overwrite columns on original data
tmpArr1 = rngTmp.Columns(1).Value
rng1 = tmpArr1
Erase tmpArr1
tmpArr1 = rngTmp.Columns(2).Value
rng2 = tmpArr1
Erase tmpArr1
'Delete temporary sheet.
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
  • Thank you very much for this script! It gave me a good starting point to complete my project :D I really appreaciate the extra text you added into it to point out how it works, will learn a lot from that! – Geoff Jul 29 '12 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.