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!

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 CutInsert Cut Cells?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

On Mon, 2 May 2011 12:39:31 -0700 (PDT), Steve
wrote:

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!

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

Thanks again!

On May 2, 6:08*pm, Gord Dibben wrote:
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

On Mon, 2 May 2011 12:39:31 -0700 (PDT), Steve
wrote:



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!- Hide quoted text -

- Show quoted text -

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!

On May 2, 9:10*pm, Steve wrote:
Thank you Gord! *Is there a way to add some error handling if the user
select cancel on either inputbox?

Thanks again!

On May 2, 6:08*pm, Gord Dibben wrote:



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

On Mon, 2 May 2011 12:39:31 -0700 (PDT), Steve
wrote:

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!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -