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

[yoree]

Thanks! I was hoping there was a shortcut!

[tonyl]

Try opening help and typing HP01216414 into the search box.

[flavet]

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?

[sdckapr]

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

[tonyl]

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.

[fburg]

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

[xlrotor]

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 Office14\XLSTART folder, so they will be there everytime Excel launches.

Cheers,

Brian
Austin, TX
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

[fburg]

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

[Frank.Laforce]

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

[flavet]

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.

[serenawilliams995]

blabla.gif



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!

[fburg]

Thank Frank for the suggestion about the columns.

Fred