How to move entire row?

J

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
 
S

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

[email protected]
[email protected] with @tiscali.co.uk
 
K

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.
 
S

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

[email protected]
[email protected] with @tiscali.co.uk
 
J

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
 
S

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

[email protected]
[email protected] with @tiscali.co.uk
 
Top