Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Changing the order of columns

    Hi Loungers

    I have a spreadsheet that contains numerous colums that is created by exporting from a web based database.

    There is no way to change the order of the colums prior to exporting so I need to be able to cut and move columns in the spreadsheet.

    I am hoping to use some code to do this as there are numerous columns to move and once the code is complete it would be used in a template to create new spreadsheets with predetermined re-ordered columns - ie column AV cut and moved to colum B - colum.

    I can sort out what needs to move to where, but after some code as an example that I can add to.

    I hope that makes sence. Any thoughts/examples would be appreciated

    Thanks

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Why not simply record a Macro of yourself cutting and pasting a column in the way you describe ?

    That will give you the starting point you are looking for - in the case of moving columns the "recorded VBA" is simple and self -explanatory.

  3. The Following User Says Thank You to MartinM For This Useful Post:

    SoxSkinsOC (2013-02-22)

  4. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Ok Thanks - will give that a go. Thanks for your assistance

  5. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    In conjunction with Martin's suggestion, I learned a very useful tip here (thanks again Rory) about moving/copying rows/columns.

    - Select the row/column to be moved.
    - Move the mouse to the edge (left or right for columns, top or bottom for rows) of what you want to move (not the edge of the col letter or row number but the edge of a cell in that col/row) and hold down the left mouse button.
    - While also holding the Shift key down (or Shift + Ctrl for copying), drag the column/row to where you want it to be. As you're dragging, you'll see a line representing the row/col you're dragging.
    Let go of the mouse button when the row/col is positioned where you want.

    It's a little hard to do at first but worth the trouble. No inserting a new row/column and cutting/pasting. You'll be able to tell you're doing it correctly if you see a single "grayish I-beam" (a line representing the row/column with a little line at the top) as you drag, as opposed to a "box" surrounding the row/column.

    I did this while recording a macro, as Martin suggested, and checked the code. It works fine.

    Fred

  6. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    One other technique that can save time is to leave a blank row at the top of your data and in that row type the order for each column that you want (1,3,5,2,4 for example) and then sort the data left to right using that row.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Rory,

    This is not so straightforward, at least in Excel 2003. I tried a small experiment with 3 columns and 3 rows. Had to select all the rows, go into the Sort dialog, go to Options, and choose sort left to right. Without all that, I had each column sorted.


    Did I miss something?

    Fred

  8. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    No - that's correct. I wouldn't do it for three columns, but if you have a lot of columns to move about it's usually faster.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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