Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    New Lounger
    Join Date
    May 2012
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Is there a "smarter" way to move a row to a different place in Excel?

    Is there a "smarter" way to move a row to a different place in your spreadsheet (without using a sort operation)?

    E.g.,
    I have a spreadsheet with 5 rows of data, Row1, Row2, Row3, Row4, Row5, etc. I want Row4 to come between Row1 and Row2. I insert a row between Row1 and Row2, then I cut Row4 and paste it to the empty row between Row1 and Row2. Then I need to return to the previous Row4 location and delete the row.

    Without using a Sort operation, is there a smarter way to do this? (If I have 500 rows, and I want Row200 to come between Row1 and Row2, I find it a bit cumbersome and tiresome to return back to the now empty Row200 place holder and delete the row....)

    Or am I just a big spoiled spreadsheet brat?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Yoree,

    Here's some VBA with error checking that will do the trick.
    Code:
    Option Explicit
    
    Sub MoveRow()
    
       Dim lDestRow   As Long
       Dim lSourceRow As Long
       Dim zMsg       As String
     
       lSourceRow = ActiveCell.Row
       zMsg = "Move Row " & Format(ActiveCell.Row) & " to Row?" & _
              vbCrLf & "Enter 0 to Exit"
       
       lDestRow = InputBox(zMsg, "Move Entire Row", 0)
       If lDestRow <> 0 And lDestRow <> lSourceRow Then
         ActiveCell.EntireRow.Select
         Selection.Cut
         Rows(lDestRow).Select
         Selection.Insert Shift:=xlDown
         
       Else
       
         If lDestRow <> 0 Then
           MsgBox "Source Row and Destination Row" & vbCrLf & _
                  "are the same -NO Action Taken-", _
                  vbOKOnly + vbInformation, _
                  "Invalid Row Move Request"
         End If
         
       End If
    To use copy into a Module in the VBA editor of the workbook in question and save the file as a .xlsm file in 2010 or 2007.
    Then just place your activecell cursor in any cell in the row to be moved, press Alt+F8, double-click the MoveRow macro and answer the prompt box. Note the row you enter will move down. The code should also work in 2003 but I haven't tested it so I don't know, but in any case in 2003 you just save the file you don't have to change the extension.

    If you're using 2010 or 2007 the manual method would be to select the entire row, press Ctrl+X, then select the Destination row and Right-Click the row number and select Insert Cut Cells. This will accomplish what you want w/o having to go back and deleting the source row.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following 2 Users Say Thank You to RetiredGeek For This Useful Post:

    Augusto (2015-08-23),simmo7 (2012-08-31)

  4. #3
    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
    Select the row you want to move. Hold the Shift key, move the mouse to the top edge of the selection, then click and drag it to where you want it to go and release the mouse.
    Regards,
    Rory

    Microsoft MVP - Excel

  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
    Hi RetiredGeek,

    I tried your macro andit works fine.

    I ran into a slight problem with macros in general: my macro settings in 2010 are set to "disable with notification" but there was no notification given when I went to run it or load the workbook. Instead, I got a msg to enable macros (not recommended) and re-open the file. So I did that and it works. But this seems odd. Help was of no help. Of course, you should remember to reset the macro security.

    Since you write lots of macros, I thought you might know about this.

    Also, I found the input box to be a little confusing so I changed my version of your VBA. The row that I enter is actually the row above the one to which the desired row is moved - kind of like an Insert.

    Fred

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Fred,

    I usually place my macro workbooks in a Trusted Location as defined in the Trust Center. Otherwise, I digitally sign them.
    I'd have to see your exact setup to know why things acted the way they did.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  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
    Hi RG,

    Thks for the answer. I think this was the first time I've run a macro in Excel 2007/10 so I was not familiar with the security safeguards there. It seems a bit different than 2003. It just seemed a bit odd that the setting (probably a default since I didn't set it) was "...with notification" yet there was no notification upon opening. Only when I went to run your macro did I get a notification - maybe that's what MS intended?

    I'll have to read a little more on the Trust Center.

    Fred

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

    Very nice feature. I never knew that and it is extremely useful for me.

    But why the top edge of the selected row? I tried it with the bottom edge and it worked fine (at least in Excel 2010, which is all I have here).

    Also I tried it with columns and it works on that too (with either the left or right edge).

    Fred

  9. #8
    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
    Why not the top edge?
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #9
    New Lounger
    Join Date
    May 2012
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RetiredGeek and rory, thank you! I've tried both and they work just fine.

  11. #10
    New Lounger
    Join Date
    Mar 2002
    Location
    Virginia
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The macro warning notification I get in Excel 2007 is a very unobtrusive single line, sort of like a row in the spreadsheet. It is _NOT_ the in-your-face MsgBox that I get in Office 2000. Maybe the macro notification is there and you just aren't seeing it. I didn't "see" it for a long while after I started using Excel 2007. I would eventually see it after a while of studying the screen for clues as to what was happening.

    Fred Holmes

  12. #11
    New Lounger
    Join Date
    Mar 2002
    Location
    Virginia
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many thanks to RG for posting this macro. It has been on my "wish list" for a long time, but I've not taken the time to really work on it. This function was a basic command in Multiplan, and when I moved from Multiplan to Excel, I really missed it.

    Multiplan has the function

    Move Row [ ] to before Row [ ], [ ] Rows

    i.e., you could move not only a single row, but a set of multiple contiguous rows in one action.

  13. #12
    New Lounger
    Join Date
    Mar 2002
    Location
    Virginia
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It would also be nice to have a modification of this macro that would allow selection of a segment of a Row to move, e.g. not the entire row, but only from Column [ ] to Column [ ], which would also be queried by an INPUTBOX.

    Perhaps all I really need is the syntax that replaces ActiveCell.Row with ActiveCell.Row.[Columns]

    Maybe I can find/guess it in the pick list.

    Many thanks,

    Fred Holmes

  14. #13
    New Lounger
    Join Date
    Mar 2011
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Select the row you want to move (row 4 in your example). Click CTRL + X
    Select the row where you want to copy your row above (row 2 in your example). Click CTRL + +
    No macro's required for that.
    Last edited by Frank.Laforce; 2012-06-21 at 12:52. Reason: made a mistake in my last step

  15. #14
    4 Star Lounger
    Join Date
    Jun 2011
    Location
    Hampshire (the old one)
    Posts
    525
    Thanks
    21
    Thanked 72 Times in 62 Posts
    It's a lot easier than all that:

    Let's say you want to move row 10 to between rows 5 and 6. Right-click row 10 (click the row number, not the cells), and click cut. Right-click row 6, and then Insert cut cells. Row 10 is now row 6, and row 6 et seq all move down one space.

    Edit: Frank.Laforce, that method will overwrite the data that's already there.
    Last edited by tonyl; 2012-06-21 at 10:27.

  16. #15
    New Lounger
    Join Date
    Mar 2011
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    tonyl,

    You're right, the last step is not CTRL + V, but CTRL + +

Page 1 of 2 12 LastLast

Posting Permissions

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