FAST CUT and PASTE
Posted by BillyBob on February 15, 2000 10:27 AM
I can turn off SCREEN REFRESH during MACRO OPS.
I want to move from SHEET A to SHEET B.
'------highlight selection
Rows("BLAH:BLAH").Select
Selection.Cut
Sheets("SHEET B").Select
'------move to end of list
ActiveSheet.Paste
Sheets("SHEET A").Select
Even with SCREEN REFRESH off, it's slow.
Ideas to make it faster?
Posted by Celia on February 15, 2000 6:41 PM
BillyBob
All of the following alternatives should be quicker since they avoid selecting rows and cells.
1. SheetA must be active and the rows to cut must be selected first :-
Selection.Cut
ActiveSheet.Paste Sheets("SheetB").Range("A65536").End(xlUp).Offset(1, 0)
2. SheetA does not have to be active and the row numbers to cut are preset (they do not have to be selected) :-
Sheets("SheetA").Rows("1:10").Cut
ActiveSheet.Paste Sheets("SheetB").Range("A65536").End(xlUp).Offset(1, 0)
3. SheetA must be active and the row numbers are specified by the EntireRow method instead of the Row method per 2 above. The EntireRow method allows for more flexibility in how to specify the rows.
This example cuts the rows that have data in column A of SheetA, starting from A1 down to the first blank cell.
Range(Range("A1"),Range("A1").End lDown)).EntireRow.Cut ActiveSheet.Paste Sheets("SheetB").Range("A65536").End(xlUp).Offset(1, 0)
All of the above alternatives will place the cut rows after the last entry in column A of SheetB. If there are no entries in column A, the paste will start at row 2 of SheetB.
Celia
Posted by Celia on February 15, 2000 6:43 PM
BillyBob
All of the following alternatives should be quicker since they avoid selecting rows and cells.
1. SheetA must be active and the rows to cut must be selected first :-
Selection.Cut
ActiveSheet.Paste Sheets("SheetB").Range("A65536").End(xlUp).Offset(1, 0)
2. SheetA does not have to be active and the row numbers to cut are preset (they do not have to be selected) :-
Sheets("SheetA").Rows("1:10").Cut
ActiveSheet.Paste Sheets("SheetB").Range("A65536").End(xlUp).Offset(1, 0)
3. SheetA must be active and the row numbers are specified by the EntireRow method instead of the Row method per 2 above. The EntireRow method allows for more flexibility in how to specify the rows.
This example cuts the rows that have data in column A of SheetA, starting from A1 down to the first blank cell.
Range(Range("A1"),Range("A1").End lDown)).EntireRow.Cut
ActiveSheet.Paste Sheets("SheetB").Range("A65536").End(xlUp).Offset(1, 0)
All of the above alternatives will place the cut rows after the last entry in column A of SheetB. If there are no entries in column A, the paste will start at row 2 of SheetB.
Celia
Posted by ivan moala on February 15, 2000 7:03 PM
Hi Billy
Another method other then Celias would be;
Sheets("SheetA").Range("A7:A14").Copy Destination:=Sheets("SheetB").Range("A7")
Ivan
