Move row to another location on sheet

S

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!
 
G

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?
 
G

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
 
S

Steve

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

Thanks again!
 
S

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top