Swapping (Rearranging) Multiple Columns of Data

[Rick Rothstein]

**** Corrected Code (Shown In Red Below) As Of October 20, 2013 ****

How do you rearrange columns of data? That question seems to come up every now and again on the various forums I frequent. As a matter-of-fact, it just came up on one yesterday to which I responded with a rather short macro. The request was this...

I have data in columns from Range (A:AJ) and I want to re arrange the same data in the following order

C,B,E,F,H,AC,K,AF,T,M,S,G,X,I,Z,AA,L,AG,AE,AH,AD,A I,A,D,J,N,O,P,Q,R,U,V,W,Y,AB,AJ

Now moving that much data around looks like it might be a formidable task, but as it turned out, only 8 active code lines, including the Dim statement, were needed. The reason such a short macro was possible is because of the tremendous power of the INDEX worksheet function which VBA provides access to. Now I could try to explain how the INDEX function works, but I'm sure my attempts would fall woefully short. I will say, though, that everything I know about the Index function came from a fantastic blog article that Daniel Ferry posted back in March 2011. Here is a link to that article which I highly recommend for reading... The Imposing INDEX

Okay, now for the macro...

Sub RearrangeColumns()
  Dim X As Long, LastRow As Long, Letters As Variant, NewLetters 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, ",")
  ReDim NewLetters(1 To UBound(Letters) + 1)
  For X = 0 To UBound(Letters)
    NewLetters(X + 1) = Columns(Letters(X)).Column
  Next
  Range("A1").Resize(LastRow, UBound(Letters) + 1) = Application.Index(Cells, Evaluate("ROW(1:" & LastRow & ")"), NewLetters)
End Sub

Simply assign the new column order to the NewOrder constant (the Const statement) as a comma delimited text string of column letters (no spaces around the commas) and run the macro. That's it... this short piece of code will do the rest... and it will do it quite quickly I might add.