Macro to move a whole row up/down

Microsoft Excel 2010
October 20, 2010 at 10:19:26
Specs: Windows XP
Hi, as well as using Excel for financial purposes, I also maintain many lists with it.

I'd like to create 2 simple macros assigned to shortcut keys that simply move the row that I'm currently in either up or down (once per execution) ie very similar to the built-in way of moving a paragraph up or down in Word.

I suspect there's a one-line answer to it . . . :-)


See More: Macro to move a whole row up/down

Report •

#1
October 20, 2010 at 10:39:06
re: I suspect there's a one-line answer to it .

Actually, if you want to be able to just keep clicking the button and have the row keep moving without reselecting it each time, it takes 3 lines for each macro.

The reason is that after the row has been moved it is no longer the current selection, so we have to select it again. Comment out the last line and run the code to see what I mean.

Sub RowDown()
  Selection.EntireRow.Cut
    Selection.Offset(2, 0).Insert Shift:=xlDown
     Selection.Offset(1, 0).Select
End Sub

Sub RowUp()
  Selection.EntireRow.Cut
    Selection.Offset(-1, 0).Insert Shift:=xlDown
     Selection.Offset(-1, 0).Select
End Sub



Report •

#2
October 20, 2010 at 16:43:25
Many thanks, DerbyDad03.

Unfortunately, I can't try it out yet as, despite spending an hour, I can't get existing Excel files to (a) open using 2010 (instead of 2007 from which I upgraded!) and (b) to open with my Personal workbook into which I've pasted the code! It's now getting on for 1am and I need to go to bed so will try again tomorrow with a more alert brain!

Thanks again


Report •

#3
October 25, 2010 at 09:25:57
[Uninstalled 2007 and also discovered that one no longer uses personal.xla!!!]

Anyway, for the record, your code works a treat - thank you - and I've also added a tweak to the RowUp macro to let the user know when they're trying to find a Row 0!

Sub RowUp()
If Not ActiveCell.Row = 1 Then
Selection.EntireRow.Cut
Selection.Offset(-1, 0).Insert Shift:=xlDown
Selection.Offset(-1, 0).Select
Else
Beep
End If
End Sub


Report •

Related Solutions

#4
October 25, 2010 at 09:37:29
re: "I've also added a tweak to the RowUp macro to let the user know when they're trying to find a Row 0"

Well, not really. All you did was cause the macro to output a Beep if the user tries to move Row 1 up. There's no reason to believe that the user will know what that beep means.

I modified the code to present a MsgBox (which also beeps, by default) to let the user know what (s)he's doing wrong.


Sub RowUp()
 If Not ActiveCell.Row = 1 Then
  Selection.EntireRow.Cut
  Selection.Offset(-1, 0).Insert Shift:=xlDown
  Selection.Offset(-1, 0).Select
 Else
  MsgBox "Row 1 Can Not Be Moved Up"
 End If
End Sub


Report •

Ask Question