Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: Swapping (Rearranging) Multiple Columns of Data

  1. #11
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    I am replying to this thread to alert anyone still subscribed to it that I have made a small, but important, change to the code posted in the original article. Not sure how I, or anyone else making use of the code, did not spot it, but without that +1 that I added to the original code (shown in red there), the output from the function will be missing a column in the output. The reason is that the Letters array is created using the Split function and the lower bound of the array produced by the Split function is always 0... that means to get a count of the number of elements in the array, you must add one to the upper bound of the array in order to include that zeroeth element into the count of elements. Apologies to anyone affected by this oversight on my part.

  2. #12
    Junior Member
    Join Date
    Jun 2014
    Posts
    1
    Rep Power
    0

    solution in 4 lines

    Sub RearrangeColumns()
    Rows(1).Insert
    Range("A1:AJ1").Value = Array(23, 2, 1, 24, 3, 4, 12, 5, 14, 25, 7, 17, 10, 26, 27, 28, 29, 30, 11, 9, 31, 32, 33, 13, 34, 15, 16, 35, 6, 21, 19, 8, 18, 20, 22, 36)
    Range("A:AJ").Sort key1:=Range("A1"), order1:=xlAscending, Orientation:=xlSortRows
    Rows(1).Delete
    End Sub

  3. #13
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by BobUmlas View Post
    Sub RearrangeColumns()
    Rows(1).Insert
    Range("A1:AJ1").Value = Array(23, 2, 1, 24, 3, 4, 12, 5, 14, 25, 7, 17, 10, 26, 27, 28, 29, 30, 11, 9, 31, 32, 33, 13, 34, 15, 16, 35, 6, 21, 19, 8, 18, 20, 22, 36)
    Range("A:AJ").Sort key1:=Range("A1"), order1:=xlAscending, Orientation:=xlSortRows
    Rows(1).Delete
    End Sub
    I used as many lines of code as I did because I wanted to make the code friendly to users by allowing them to specify the columns using letter references instead of making them count the columns in order to figure out their column numbers. However, going with your specified array, and if we don't count Dim statements as "live" code, I can do it in two lines of code...

    Code:
    Sub RearrangeColumns()
      Dim NewOrder As Variant
      NewOrder = Array(23, 2, 1, 24, 3, 4, 12, 5, 14, 25, 7, 17, 10, 26, 27, 28, 29, 30, 11, 9, 31, 32, 33, 13, 34, 15, 16, 35, 6, 21, 19, 8, 18, 20, 22, 36)
      Range("A1").Resize(Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row, UBound(NewOrder) + 1) = Application.Index(Cells, Evaluate("ROW(1:" & Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row & ")"), NewOrder)
    End Sub
    and if we do not mind counting the number of array elements manually, that can be reduced to a single line of code (albeit a very long one)...

    Code:
    Sub RearrangeColumns()
      Range("A1").Resize(Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row, 36) = Application.Index(Cells, Evaluate("ROW(1:" & Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row & ")"), Array(23, 2, 1, 24, 3, 4, 12, 5, 14, 25, 7, 17, 10, 26, 27, 28, 29, 30, 11, 9, 31, 32, 33, 13, 34, 15, 16, 35, 6, 21, 19, 8, 18, 20, 22, 36))
    End Sub
    Last edited by Rick Rothstein; 06-13-2014 at 02:34 AM.

  4. #14
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    12
    Wow. This is an older thread, but as far as the content is concerned it is extremely useful. This thread provides a number of great ways to rearrange columns. I have to say that in the past I was using a greatly inferior method. I wanted to throw in a Thank You and let you know that people can definitely still get value from posts whether they are current or not. I know that with my personal experience, I commonly run across threads started in the early 2000's that are extremely useful. Keep up the good work!
    Last edited by mrmmickle1; 07-15-2014 at 05:25 AM.
    Using Excel 2010

Similar Threads

  1. Replies: 2
    Last Post: 03-05-2013, 07:34 AM
  2. Converge Data From Multiple Columns To Single Column
    By ayazgreat in forum Excel Help
    Replies: 3
    Last Post: 12-14-2012, 10:55 PM
  3. Swapping columns Error 2
    By jomili in forum Excel Help
    Replies: 1
    Last Post: 11-16-2012, 08:52 PM
  4. Swapping Multiple Columns of Data
    By jomili in forum Excel Help
    Replies: 5
    Last Post: 10-01-2012, 05:56 PM
  5. Replies: 2
    Last Post: 06-14-2012, 04:10 AM

Tags for this Thread

Posting Permissions

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