VB code for Moving Rows up and down

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AndyR - 10 Aug 2005 04:34 GMT

Hi, I have tried a search but could not find what I am looking for.

I have a spreadsheet and would like to be able to move rows up and down
at the press of a button, so if I wanted to move row 20 to row 19,
highlighting a cell in row 20 and then a click of a button would swap
the two rows, I could then carry on and move the row all the way to the
top if need be.

Is this too much to ask? :(

Any suggestions would be much appreciated.

Regards

Andy

Signature

AndyR

Reply to this Message

AndyR - 10 Aug 2005 05:11 GMT

Maybe a bit hasty psoting the message above, after sitting down and
trying it out this simple code seems to work, can anyone see any
tidying up or glaring ommissions that I have made?

Cheers

Andy

Sub MoveDown()

Selection.EntireRow.Select
Selection.Cut
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-1, 0).Range("A1").Select

End Sub

Sub MoveUp()

Selection.EntireRow.Select
Selection.Cut
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.EntireRow.Select
Selection.Insert Shift:=xlDown

End Sub

Signature

AndyR

Reply to this Message

anthony rose - 21 Feb 2006 11:16 GMT

Hi, I'm not trained in VB so may be missing something but this seems tider
because it doesn't change the user's selection and has less lines. Also it
checks for top row.

Sub MoveDown()

Range(ActiveCell.Row + 1 & ":" & ActiveCell.Row + 1).Cut 'Cut row below
ActiveCell.EntireRow.Insert Shift:=xlDown 'Paste
ActiveCell.Offset(1, 0).Select 'Move down to it

End Sub

Sub MoveUp()

If ActiveCell.Row > 1 Then
Range(ActiveCell.Row & ":" & ActiveCell.Row).Cut 'Cut active row
ActiveCell.Offset(-1, 0).Select 'Move up a row
ActiveCell.EntireRow.Insert Shift:=xlDown 'Paste
End If

End Sub

Reply to this Message