Sorting two non-adjacent columns without affecting any other column

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"

1 Answer

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 tmpS.Delete Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub