I have to do some manual reordering or rows in Excel and cut/paste is too hard to use. I'm looking for something that would enable me to use drag'n'drop or to add some buttons to move-up/down move-top/bottom.
Add the following macros to your Personal Macro Workbook and assign them shortcut keys. The behaviour mimics Sublime Text's Swap Line Up & Swap Line Down.
Sub move_rows_down() Dim rOriginalSelection As Range Set rOriginalSelection = Selection.EntireRow With rOriginalSelection .Select .Cut .Offset(rOriginalSelection.rows.Count + 1, 0).Select End With Selection.Insert rOriginalSelection.Select End Sub Sub move_rows_up() Dim rOriginalSelection As Range Set rOriginalSelection = Selection.EntireRow With rOriginalSelection .Select .Cut .Offset(-1, 0).Select End With Selection.Insert rOriginalSelection.Select End Sub
For snobs like me who want to use keyboard only:
- Select the cells you want to move (rows or columns, or any rectangle of cells, but the selection must be contiguous)
- Press Ctrl+x (Ctrl+c would let you make two copies.)
- Move to or select the cell immediately after where you want to move the content (below it, or to the right.) This cell will not move.
- Press Ctrl++. This will shift cells down or to the right. (Ctrl+v would overwrite cells instead.)
On the Mac, all of these shortcuts work the same with either Ctrl or ⌘.
In dealing with similar cases in the past, where I could not just sort by a row, I found way to generate a column with a formula result that was something I could sort on.
I found a more direct answer to your question from this site:
Microsoft Word has a feature which Excel is lacking. Jon's method involves moving the data to Word, employing the Word command and then pasting the data back to Excel. Follow these steps.
- Copy the relevant chunk of rows and columns out of your speadsheet. It is best to note the size of the range, e.g., 118 rows x 5 columns
- Paste the data into a Microsoft Word document, where it automatically becomes a table and retains all your formatting.
In Word, use the little-known SHIFT-ALT-UP-ARROW and SHIFT-ALT-DOWN-ARROW to very speedily slide rows (or selected chunks of rows) up and down at will. Select one or more rows. You can select the entire row or just a portion of the row as shown here.
Hit Shift+Alt+UpArrow several times in order to quickly slide the rows up into position.
When you have sequenced the rows as you like, paste them back into Excel, making sure you overwrite the exact same size chunk you copied.
Here is a sub that also works for columns; it combines the functionality for all four directions:
Sub MoveRowsOrColumns(direction As String) Dim rOriginalSelection As Range Select Case direction Case "up", "down" Set rOriginalSelection = Selection.EntireRow Case "left", "right" Set rOriginalSelection = Selection.EntireColumn Case Else Debug.Assert False End Select With rOriginalSelection .Select .Cut Select Case direction Case "up" .Offset(-1, 0).Select Case "down" .Offset(rOriginalSelection.Rows.Count + 1, 0).Select Case "left" .Offset(0, -1).Select Case "right" .Offset(0, rOriginalSelection.Columns.Count + 1).Select End Select End With Selection.Insert rOriginalSelection.Select End Sub