Using Array in Auto Filter VBA -Type 13 error - more than 65536 records

I've scoured all over the forums and message boards before posting this...

So here is my requirement step by step

1. I have a data in worksheet (240k rows) - each row has a unique transaction id
2. I need to filter that data with criteria that is selected from a range
3. For that I have used a For Loop and to construct an array and used that Array to put in auto filter
So far all good - no problem
4. Now the problem is that - the range I'm using to make the array and put it the filter has more than 2^16 records [68k records] which is giving me a type mismatch error - and I don't know how to solve it - some suggest transpose or loops but consider me a full novice in transposing and how it works or what it does.


Below is the code extract

Please Login or Register  to view this content.

Thanks,
Karan

Why not use an Avanced Filter and use the data where it exists?

It would help if you can upload small sample demonstrating your set up and issue.

One thing, is that you don't need to use ReDim Preserve...

Since from your code, it looks like you know the upper bound of the array already (i.e. SH2.Cells(5, 14))

So you should be able to write...

Please Login or Register  to view this content.

Originally Posted by TMS

Why not use an Avanced Filter and use the data where it exists?

Hi,

Thanks for the quick reply and apologies for my late response.

So the problem with advanced filter is that it gets stuck --- its really very slow compared to autofilter - Even on mt Surfacebook or Thinkpad it's crashing half the time

My observations

Auto Filter:- 9k record filter - 3 seconds
Auto Filter:- 25k record filter - 5-6 seconds
Auto Filter:- 60k record filter - 10 seconds max
Auto Filter:- 68k record filter - error
Advanced Filter:- 25k records - usually crashes - if resulted goes above 5-6 minutes easy

Running i5 8GB RAM Surface Book / Thinkpad both

Advanced Filter observation is same whether I run with macro or using standard feature.