How can I streamline/ speed up my code- it is currently crashing excel

I have a code working fine- it looks to see whether data in column F matches a selection from a drop down list- and if it does copies across the respective data in column G to a new worksheet. Then it removes duplicates.

I have tested it on a sample workbook.

The problem is the real thing works from a sheet with over 100,000 rows of data and so just crashes excel every time it runs.

Anybody got any ideas how I might streamline it/ make it run faster....the code is:

Please Login or Register  to view this content.

Use filter. Please attach the sample.

I can only attach a sample workbook so the data is desensitised- but this wouldn't show how it struggles with the large number of rows.

Is it best to upload a sample workbook so you can see what's going on anyway?

Strud,
Remove any confidential information. We are only interested to see the layout and format of your data. We also need to test the code. Please attach your sample.

Ok so the codes I mean run when a selection is made on the 'Brands' and 'Parent' drop down lists on sheet2- you will see the data A1:C10 change.

The sample static data is in sheet1. It will in reality be almost 90,000 rows as I mentioned.

So that when say a selection is made on the Parent list excel crashes.

Hope that makes it clearer.

Thanks for that Joseph but unfortunately the following part of the code comes up as an error...

Originally Posted by JosephP

Please Login or Register  to view this content.

Have you tried Jay's code on your actual data? If it works, it is very fast.

Yeah as I say

Please Login or Register  to view this content.

prompted the debugger

Actually looking back at your original code I think that's what it was and I somehow put that .Value in or left it in from before or something.

Anyway- just tested the code and it works perfectly- rapid enough to be instantaneous pretty much.

Massive thanks for your help Joseph!!

One more question if I may Joseph- How would I add into your code a sort function so the copied data in the new destination is sorted alphabetically?