Swapping (Rearranging) Multiple Columns of Data

[Rick Rothstein]

Sub RearrangeColumns()
  Dim X As Long, LastRow As Long, Letters As Variant
  Const NewOrder As String ="C,B,E,F,H,AC,K,AF,T,M,S,G,X,I,Z,AA,L,AG,AE,AH,AD,AI,A,D,J,N,O,P,Q,R,U,V,W,Y,AB,AJ"
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  Letters = Split(NewOrder, ",")
   For X = 0 To UBound(Letters)
    Letters(X) = Columns(Letters(X)).Column
  Next
  Range("A1").Resize(LastRow, UBound(Letters)) = Application.Index(Cells, Evaluate("ROW(1:" & LastRow & ")"), Letters)
End Sub

Sub snb()
  sn = Split("C,F,A,G,B,J,E,D,C,H", ",")
  For j = 0 To UBound(sn)
    sn(j) = Columns(sn(j)).Column
  Next

  Cells(1).CurrentRegion.Rows(1).Offset(Cells(1).CurrentRegion.Rows.Count) = sn
  With Cells(1).CurrentRegion
    .Sort Cells(.Rows.Count, 1), , , , , , , , , , xlSortRows
    .Rows(.Rows.Count).ClearContents
  End With
End Sub

While I haven't tried it out, your code looks like it should work, but personally, I think your "sort order string" would be harder to set up than mine. With mine, you just look at the columns in the order you want them to be ("I want C first, then B, then E, etc.") and then write the column letters down in that order... easy to do by sight. With your method, I would have to know where the column will end up at after the sort so that I can write those column letters down in that order instead ("A will be C in the new order, B will be F in the new order, C will be A in the new order, etc.)... which, to me, does not seem as easy to do. However, you have offered a valid alternative approach to the problem, so I thank you for that.