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

**yoree** · 2012-06-08 13:54

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?

**RetiredGeek** · 2012-06-08 15:46

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.

**rory** · 2012-06-11 07:17

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.

**fburg** · 2012-06-12 09:19

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

**RetiredGeek** · 2012-06-12 15:23

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.

**fburg** · 2012-06-13 09:33

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

**fburg** · 2012-06-14 08:15

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

**rory** · 2012-06-14 08:17

Why not the top edge?

**yoree** · 2012-06-15 11:17

RetiredGeek and rory, thank you! I've tried both and they work just fine.

**fholmes** · 2012-06-21 07:25

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

**fholmes** · 2012-06-21 07:30

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.

**fholmes** · 2012-06-21 07:39

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

**Frank.Laforce** · 2012-06-21 08:46

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.

**tonyl** · 2012-06-21 09:22

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.

**Frank.Laforce** · 2012-06-21 11:51

tonyl,

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

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

Thread Tools