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

Topic

New Reply

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?

Reply | Quote

Replies

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Why not the top edge?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

tonyl,

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

Reply | Quote

I tried Rory’s method – it worked great. I then tried Frank’s original method and it did not work correctly; it left the row moved blank and the row at the new location overwritten. Later, I went to EXCEL’s help screen and tried their method – it worked like Frank’s method – not desired. I could not find in EXCEL’s help a way to merely move a row from one location to another without overwritting a row at the new location (I did not try EXCEL’s method that involved cutting and pasting, which makes the process a bit harder).
Would using EXCEL’s method of cutting and pasting have worked without the problems I cite here?

Reply | Quote

I tried Rory’s method – it worked great. I then tried Frank’s original method and it did not work correctly; it left the row moved blank and the row at the new location overwritten. Later, I went to EXCEL’s help screen and tried their method – it worked like Frank’s method – not desired. I could not find in EXCEL’s help a way to merely move a row from one location to another without overwritting a row at the new location (I did not try EXCEL’s method that involved cutting and pasting, which makes the process a bit harder).
Would using EXCEL’s method of cutting and pasting have worked without the problems I cite here?

Excel’s method (cut & Insert cut cells, not paste) definitely works. If you simply cut & paste, you will move the cells from one location to another, but not the row itself, thus overwriting the data. That is similar to the method that Frank suggested, which I can’t seem to make work. I’ll keep trying, though. Rory’s method does work, and may be quicker and easier, but you have to be accurate with your mousing.

Personally, although I’m a keyboard person, I find it a bit of a faff trying to give the row number the focus, so I can get the context menu. So I just use the mouse and have done with it.

Reply | Quote

Apparently Rory’s method also works with moving 1 or more cells, rather than an entire row. Select a range of cells, grab the top of the selection box (or the bottom) and move the cells with the Shift key held down. When you release the mouse, the selected cells are placed where the “I beam” was. Cells in the same column are moved up.

Fred

Reply | Quote

That is indeed a handy way to move a row up or down. It is worth remembering.

The Excel development team must not use Word. Word will do essentially this by pressing Shift+Alt+uparrow and downarrow. This works in a Word table and also in bullited and numbered lists. Once you’ve used these a few times, you see how handy they can be. It’s too bad Excel doesn’t do the same thing.

In Excel these keystrokes do not seem to do anything. So what the heck…..

I used the Excel keyboard actions (given in the earlier post) with the excel 2010 macro recorder turned on, to move a row up one row. The recorder gave me the first macro given below. Using this as a model, I created two versions to move the rows containing the currently selected cells, one for up and one for down. There is also a macro to assign these to the keyboard combos Shift+Alt+Up and Down. The Rows.Count part enables them to move either a single row or a block of rows. These seem to work like a charm, and I have added them to my PERSONAL.XLS file that I keep in the Office14XLSTART folder, so they will be there everytime Excel launches.

Cheers,

Brian
Austin, TX
http://www.xlrotor.com
Sub MoveRowUp()
‘
‘ MoveRowUp Macro
‘
Rows(“6:6”).Select
Selection.Cut
Rows(“5:5”).Select
Selection.Insert Shift:=xlDown
End Sub
Sub myMoveRowUp() ‘this works with one or more rows
If TypeName(Selection) “Range” Then Exit Sub
Selection.EntireRow.Select
Selection.Cut
Selection.Offset(-1, 0).EntireRow.Select
Selection.Insert Shift:=xlDown
End Sub
Sub myMoveRowDown() ‘needed some extra logic to handle multiple rows
If TypeName(Selection) “Range” Then Exit Sub
Selection.EntireRow.Select
Selection.Cut
Selection.Offset(Selection.Rows.Count + 1, 0).EntireRow.Select
Selection.Insert Shift:=xlDown
Selection.Offset(-Selection.Rows.Count, 0).EntireRow.Select
End Sub
Sub Auto_Open() ‘assign SHIFT+ALT+ARROW to run the macros
Application.OnKey “+%{UP}”, “myMoveRowUp”
Application.OnKey “+%{DOWN}”, “myMoveRowDown”
End Sub

Reply | Quote

And today I had a need to copy 2 rows to another location between 2 other rows. The combination of SHIFT+CTRL+drag from the top/bottom of the selected rows dumped a copy exactly where I wanted them.

Speaking of necessity being the mother of invention!

Do you think it will work with columns?

Fred

Reply | Quote

To move columns, you can use the same shortcut as for rows:

Select the column(s) you want to move. Click CTRL + X
Select the column where you want to copy your column(s) to the left of it. Click CTRL + +

Frank

Reply | Quote

flavet here – I don’t know why I received an email today about this thread, except that it may be the way this site’s process works – if you request to be notified about postings, you will be emailed.
I was not the originator of this thread. I posted to it in post #18. That post contained a request for help in using a method earlier in the tread. My post was made two months ago, and six other posts followed mine, with the most recent one dated today.
Never the less I believe one or two of the posts made after my post and somewhat before today’s answered my request.

Reply | Quote

Thanks! I was hoping there was a shortcut!

Reply | Quote

Try opening help and typing HP01216414 into the search box.

Reply | Quote

I usually right click the row (10) and select Cut

Then right click the place to insert (row 6), then enter insert cut cells. It inserts row 10 between 5&6 and removes rows 1-5 and 11-end are unaffected. row 10 becomes 6, 6 becomes 7, 7 to 8, 8 to 9, 9 to 10.

Steve

Reply | Quote

31422-blabla

Start Microsoft Excel, and open the file you want to change.
2

Click on the cell immediately below where you want the new row to appear.

Sponsored Links
OBD 2 Software for OSX

3

Open the Insert menu, and select Rows. The new row will appear above your current selection.
Inserting a New Column
4

Start Microsoft Excel, and open the file you want to change.
5

Click on the cell immediately to the left of where you want the new column to appear.
6

Open the Insert menu, and select Columns. The new column will appear to the left of your current selection.

Sponsored Links

Automate tasks, reportswww.orchidsystems.biz

Sourcing New Products From China Contact Export-ready Suppliers Now
PowerPivot Trainingwww.sswug.org

Access Videos, Articles and More. Join the SSWUG.ORG Community Today!
Free Blog Promotion Toolwww.buzzbaiting.com

Learn How To Get More Visitors. Marketing Your Blog Is Now Easier!

Reply | Quote

Thank Frank for the suggestion about the columns.

Fred

Reply | Quote