VBA Code to Select Multiple Rows and Copy

[kinseld5]

Hi,

Please see below code:

Please Login or Register  to view this content.

This will run through each row and if the criteria met will copy that row to another tab and so on until all rows are scanned. Unfortunately its very slow. Is there a way to select all the rows that meet the criteria (a Ctrl + select method) and after all cells selected copy the data over as a whole thus saving time?

Any help is greatly apprecaiated.

[arlu1201]

Try this

Please Login or Register  to view this content.

Change the parts marked bold.

[kinseld5]

Thanks for the comment
The issue lies with the amount of rows that excel can action the 'visible cells only' command (which I tried to use but I got an error stating the action is too complex).

The reason I ask for the method stated in the earlier post is to counteract this problem. Any suggestions welcome?

[kinseld5]

Just to note, I have in excess of 260,000 rows in the worksheet

[arlu1201]

Is it fine to sort the data? You can sort the data based on the column and so all the 1, 2 and 3 will appear on top. This way, the macro will work faster. We can ask the macro to stop once it reaches a value >3.

Do you want a code to do this?

[kinseld5]

Thats a great idea.

The sorting part can be completed manually. I don't mind that but for the remaining code I presume I just write:

Please Login or Register  to view this content.

[arlu1201]

Yes, there are many ways of doing it. You can try whichever way you are comfortable in. If you face any issues, you can post it back here.