Swapping Multiple Columns of Data

**jomili** · 09-28-2012, 01:37 AM

Rick,

I'm using your SWAP Columns macro , and am having an issue. See the attachment with before and after sheets. I've posted the macro below, configured the way I'm using it. The problem is that Column Z, "Contract Manager Region" in the "Before" tab, is being deleted in the "After" tab, and Column AE, "Subject" in the "Before" tab, is being correctly moved to Column E in the "After" tab, but is also staying in AE in the "After" tab, where "Contract Manager Region" should be. Do I have something configured wrong?

Code:

Sub RearrangeColumns()
'http://www.excelfox.com/forum/f22/swapping-rearranging-multiple-columns-data-493/
'in the code as expressed below, column C moves to first position, B stays in second, E is thrd, etc.
'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"
  
  Dim X As Long, LastRow As Long, Letters As Variant
  'Type in order of what column you want in what position
  'Make sure there are no spaces in this string
  Const NewOrder As String = "D,E,AC,AD,AE,AB,B,C,F,G,H,I,J,K,L,M,N,P,X,Y,Q,R,S,T,U,V,W,A,AA,O,Z"
  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

**Admin** · 09-28-2012, 08:31 AM

Hi jomili,

Welcome to ExcelFox !!

Please do not ask questions on Rick's blog. Ask question on Excel help forum and refer the thread if you face any problems on Rick's code.

This time I added the reference and moved your post to Excel forum. I appreciate if you follow this rule in your future posts.

**jomili** · 09-28-2012, 05:37 PM

Thanks, I didn't know any better, so I'll try to remember that in the future.

**jomili** · 09-28-2012, 06:31 PM

Update: I've got a workaround. If create column "AF", "Trash Column", and add AF as the last item in my string of columns (see below), it gets left as the last column, whereupon I can delete it and get what I need. This will work, but I'd be interested in seeing it work without the workaround.

Code:

Const NewOrder As String = "D,E,AC,AD,AE,AB,B,C,F,G,H,I,J,K,L,M,N,P,X,Y,Q,R,S,T,U,V,W,A,AA,O,Z,AF"

**Rick Rothstein** · 09-29-2012, 09:24 PM

Originally Posted by jomili

Update: I've got a workaround. If create column "AF", "Trash Column", and add AF as the last item in my string of columns (see below), it gets left as the last column, whereupon I can delete it and get what I need. This will work, but I'd be interested in seeing it work without the workaround.

Code:

Const NewOrder As String = "D,E,AC,AD,AE,AB,B,C,F,G,H,I,J,K,L,M,N,P,X,Y,Q,R,S,T,U,V,W,A,AA,O,Z,AF"

I have confirmed the bug you uncovered in my original code and have corrected it. The code appearing in Message #1 of the my "Swapping (Rearranging) Multiple Columns of Data" article is now correct as shown. Thank you for uncovering the problem!