Array slicing optimisation

[Kyle123]

Hi All,

I am optimising some code and need to be able to slice large arrays and am hoping that there is a more efficient way.

The arrays are 40k lines plus.

I currently loop through three workbooks, each with 40k lines and, I put each range directly into a variant array, then into an array.

I then loop through the array to access each sub array and convert each sub array to 1d so I can use the filter function. I have tried using INDEX and tried transposing the data from each workbook, both error, which I'm assuming is due to the size of the data.

Currently I'm using the below (which is an excerpt):

Please Login or Register  to view this content.

To give a bit more background, the files are TabDelimited and I need to find given words (60+) that may exist on any row, and in any of a number of fields on each row.

I've also tried Querying the workbooks with ADO and filtering the recordsets, but this was unbelievably slow.

Any ideas as to whether this can be sped up at all? / Better way of attacking the problem?

Cheers

Kyle

[OnErrorGoto0]

Did you try ADO (either UNION query or three separate queries), then GetRows to put the data into an array? You could also restrict the data using a WHERE clause with Like (though that might not be very fast).

[Kyle123]

Yes, I tried that, but GetRows() still returns a 2d array which needs to be sliced. Also I can restrict the data, but I'd need to run more queries :S

Looks like looping through might be the only option then

Thanks for looking

[snb]

Much simpler ( and faster !):

Please Login or Register  to view this content.

sn is a 0-dimensional array containing all 'rows' in the csv-file that contain the searched for textstring.

You can even add the content of all csv files into 1 string before splitting and filtering.

[nilem]

as an option

Please Login or Register  to view this content.

*it works with Russian local settings

[Kyle123]

Great suggestions, Thanks