How to move entire row?

[Jack]

Hello,
I am using visual basic and Excel automation.
How can I programmatically move entire row to the end and shift all other
rows one row up?
Jack

 

[Sandy Mann]

Assuming that you want to select the row to be moved by selecting a cell in
it and that Column A entried in it to the end of the data then try
something like this:

Sub MoveIt()
Dim EndOfData As Long
Dim Acr As Long

EndOfData = Cells(Rows.Count, 1).End(xlUp).Row + 1

Acr = ActiveCell.Row

Rows(Acr).Cut Destination:=Rows(EndOfData)
Rows(Acr).Delete Shift:=xlUp

End Sub


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Jack]

Thank you.
It does work wonderfully.
What is it xlUp?
Jack

 

[KC Rippstein]

You are telling Excel to go to the End (row 65536 in Excel 2003) and then do
a Ctrl+Up (xlUp) to go to the last row with data.

 

[Sandy Mann]

Also, I used Cells(Rows.count,1) rather than cells(65536,1) which in all
versions from XL97 to XL2003 would suffice, because as others have taught me
in these NG's, it makes it usable in all versions of XL including XL95 (with
16,384 rows) XL2007 (which they tell me has 1,000,000 rows)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Jack]

Sandy, I have modified your code a little:

Public Sub XLMoveToLast()
Dim EndOfData As Long
EndOfData = moExcelWS.UsedRange.Rows(moExcelWS.UsedRange.Rows.Count).Row
+ 1
moExcelWS.Rows(CurrentRow).Cut Destination:=moExcelWS.Rows(EndOfData)
moExcelWS.Rows(CurrentRow).Delete Shift:=xlUp
End Sub


What do you think about that?
Jack

 

[Sandy Mann]

Jack,

You are using keywords not available to me in XL97 so I cannot say if there
is anything that should be changed - perhaps others with later versions will
chip in and comment on your code.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

Jack,

One potential problem that you could have is your use of UsedRegion. MVP
Rodney Powell explains it here:

http://www.beyondtechnology.com/geeks012.shtml

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk