Move row to another location on sheet

[Steve]

Hi all. Is there a way to move a row of data to another location on
the sheet? I'd start with the active cell (row), and when the code
executes be prompted with "what row would you like to move the current
row to?". Then obviuosly move, for example, row 32 up to row 5,
moving all other rows down one.

Thanks!

 

[GS]

Steve wrote on 5/2/2011 :

Hi all. Is there a way to move a row of data to another location on
the sheet? I'd start with the active cell (row), and when the code
executes be prompted with "what row would you like to move the current
row to?". Then obviuosly move, for example, row 32 up to row 5,
moving all other rows down one.

Thanks!

Is there some reason you can't use Cut>Insert Cut Cells?

 

[Gord Dibben]

Option Explicit
Sub move_row()
Dim sourcerow, targetrow As Range
Set sourcerow = Application.InputBox(prompt:= _
"Select Any Cell in Row to Move", Type:=8)
Set targetrow = Application.InputBox(prompt:= _
"Select Any Cell in Row for Insertion", Type:=8)
sourcerow.entirerow.Cut
targetrow.Insert shift:=xlDown
Application.CutCopyMode = False
End Sub


Gord Dibben MS Excel MVP

 

[Steve]

Thank you Gord! Is there a way to add some error handling if the user
select cancel on either inputbox?

Thanks again!

 

[Steve]

Gord,

I added this after each inputbox group of code:
If sourcerow Is Nothing Then
Exit Sub
End If

Not sure if Exit Sub is a preferred method of error handling, but it
worked!

Thanks again for your help!