If value is in array then copy row to another sheet

[DanzaNZ]

Hi all

I am very new to arrays and wanting to use a better way to search a sheet for a value and copy the rows to a new sheet.

I currently have this code which works well but considering the sheet it is looking in as a lot of data, it takes far too long

Please Login or Register  to view this content.

What i am wanting it to look for a specific value (EMP_REF), and copy all rows to another sheet.
Any suggestions on how to make the data it is searching into an array?

Any help is much appreciated!

[sktneer]

No need to loop through the cells to check for specific value, just use Autofilter to filter in the rows where column A has the desired value and then copy the visible rows to the desired sheet in one go.

Or Advanced filter is also a good option if you set a criteria range and that would be the fastest method to copy the data to another location.

[DanzaNZ]

I have figured out how to add the sheet to an array and search it, but i want to be able to add the found row in the array to another array....

Here is what I have so far, but the parts commented out is where i am struggling.

Please Login or Register  to view this content.

Would really appreciate help with this