Results 1 to 6 of 6

Thread: Swapping Multiple Columns of Data

  1. #1
    Junior Member
    Join Date
    Jul 2011
    Posts
    19
    Rep Power
    0

    Swapping Multiple Columns of Data

    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
    Attached Files Attached Files
    Last edited by jomili; 09-28-2012 at 05:40 PM. Reason: added ref

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Jul 2011
    Posts
    19
    Rep Power
    0
    Thanks, I didn't know any better, so I'll try to remember that in the future.

  4. #4
    Junior Member
    Join Date
    Jul 2011
    Posts
    19
    Rep Power
    0
    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"

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by jomili View Post
    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!

  6. #6
    Junior Member
    Join Date
    Jul 2011
    Posts
    19
    Rep Power
    0
    Glad I could help. Pointing out problems is the easy part. Thank you for doing the hard part.

Similar Threads

  1. Swapping (Rearranging) Multiple Columns of Data
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 13
    Last Post: 07-15-2014, 05:21 AM
  2. Replies: 2
    Last Post: 03-05-2013, 07:34 AM
  3. Converge Data From Multiple Columns To Single Column
    By ayazgreat in forum Excel Help
    Replies: 3
    Last Post: 12-14-2012, 10:55 PM
  4. Swapping columns Error 2
    By jomili in forum Excel Help
    Replies: 1
    Last Post: 11-16-2012, 08:52 PM
  5. Replies: 2
    Last Post: 06-14-2012, 04:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •