easiest fastest way to copy entire rows

[philfer]

Hi,



I have a worksheet in a workbook with lots of ledger codes. I want to loop down column A and when it finds a particular code saya 2010 I want to copy the entire row into another worksheet so that the new worksheet only has the details for this ledger code 2010.



I am using a loop (For i = 1 to lastrow) and then doing



Activesheet.Cells(i,1).EntireRow Copy Sheet("Sheet2").Range(j,1)



(j is obtained by finding the lastrow on the new sheet then adding 1 each time 2010 is found)



but it is SLOOOOOOOW.



It takes forever (well about a minute but in macro time it seems like forever)



Is there a quick way of doing this

 

[PHV]

Use an AutoFilter.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xlbo]

...or the FIND method will reduce the outer loop time

For the inner loop, there is no real need to use the COPY method - you could assign the relevant details directly to the cells you want the info in. If your data never goes beyond a certain column, why use the entire row? This will slow down the process

const lCol = 45
'change this based on what will be the last column of data for you

("Sheet2").Range(j,lCol)=Activesheet.Cells(i,lCol)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Wally9]

Adding

application.screenupdating =false

would also increase speed