Excel VBA Need to rotate players - find / copy last 3 rows and move to the top
 

Thanks for helping - Excel 2002
Overview: Need to find the last 3 rows of a range and move them to the top
of the range
1: I need to look for the Last cell of value (Player's name) in a row in Col
C starting at C1012
2: Select the values in that row C D E F as well as the 2 rows above (3 in
all)
3. Block select and move them to the top of new range (Players in same order)
4: Move the rest of the original range below those 3 rows

Your help is appreciated and I promoise to help others

Excel VBA Need to rotate players - find / copy last 3 rows and move to the top
 

Sub AA()
Dim rng As Range
Set rng = Cells(1012, "C").End(xlUp).Offset(-2, 0).Resize(3, 4)
rng.Cut
Range("C1:F1").Insert Shift:=xlShiftDown

End Sub


--
Regards,
Tom Ogilvy



"sam s via OfficeKB.com" wrote in message
...
Thanks for helping - Excel 2002
Overview: Need to find the last 3 rows of a range and move them to the
top
of the range
1: I need to look for the Last cell of value (Player's name) in a row in
Col
C starting at C1012
2: Select the values in that row C D E F as well as the 2 rows above (3 in
all)
3. Block select and move them to the top of new range (Players in same
order)
4: Move the rest of the original range below those 3 rows

Your help is appreciated and I promoise to help others

Excel VBA Need to rotate players - find / copy last 3 rows and move to the top
 

Thank you Tom for your quick response. I just got back and looking forward
to trying your solution.


--
Message posted via http://www.officekb.com

Excel VBA Need to rotate players - find / copy last 3 rows and move to the top
 

Thank you tom - You are a genius- I can't believe how such a small piece of
code did EXACTLY what I needed. Thanks again!!

sam s wrote:
Thank you Tom for your quick response. I just got back and looking forward
to trying your solution.


--
Message posted via http://www.officekb.com

Excel VBA Need to rotate players - find / copy last 3 rows and move to the top
 

One problem I found several hours later. If I hide the rows below 2000 to
end and run the macro. It takes over a minute whereas unhidden takes 3
seconds. I believe it is the shift:=xlshiftdown:. Is there a substitue if I
only need to shift down 200 rows. Thanks

sam s wrote:
Thank you tom - You are a genius- I can't believe how such a small piece of
code did EXACTLY what I needed. Thanks again!!

Thank you Tom for your quick response. I just got back and looking forward
to trying your solution.


--
Message posted via http://www.officekb.com