Swapping (Rearranging) Multiple Columns of Data

[snb]

To overcome the restrictions (<= 26 columns) of my earlier posted code I made up this one, in which the sorting essentially takes place in a oneliner code.

Sub snb()
  Cells(1).CurrentRegion.Name = "snb_002"
  Names.Add "snb_01", [{"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"}]

  [snb_002].Offset(10) = Application.Index([snb_002], [row(snb_002)], [transpose(match(index(snb_01,,column(snb_002))&1,address(1,row(1:52),4),0))])
end sub

I have been looking for a solution in which the Application.Index would have been part of the 'between brackets' evaluation, but I wasn't able to construct a successful one.

[Rick Rothstein]

To overcome the restrictions (<= 26 columns) of my earlier posted code I made up this one, in which the sorting essentially takes place in a oneliner code.

Sub snb()
  Cells(1).CurrentRegion.Name = "snb_002"
  Names.Add "snb_01", [{"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"}]

  [snb_002].Offset(10) = Application.Index([snb_002], [row(snb_002)], [transpose(match(index(snb_01,,column(snb_002))&1,address(1,row(1:52),4),0))])
end sub

I have been looking for a solution in which the Application.Index would have been part of the 'between brackets' evaluation, but I wasn't able to construct a successful one.

Sorry, I cannot agree with you that your code "essentially takes place in a oneliner code"... it looks like three lines to me. On top of that, I think you should be "nice" to the person running macro and provide addition code to remove the Defined Names you created within the code. As for the square bracket form of Evaluate... besides, if memory serves correctly, it being documented as slower than using the Evaluate function directly, you cannot construct expressions to evaluate by concatenating text and variables from you VB code together the way you can with the Evaluate function (which takes a String argument).