Large Database - VBA Formulas

[jsneak]

Hi all,

Currently have a spreadsheet that has some VBA written inside that imports a .csv file that is about 27mb (150k+ rows of data) and its has to do some vlookups, then after that filters the data. I'm just wondering if there was a different way this could work? The machines we currently use aren't the best so it takes about 3/4minutes to run through everything. Any help on this would be brilliant.

Please Login or Register  to view this content.

Thanks!

[TMS]

Well, you should avoid selecting anything. For example, this

Please Login or Register  to view this content.

can be this

Please Login or Register  to view this content.

You also use a double VLOOKUP with ISNA in some places and the more efficient VLOOKUP with IFERROR elsewhere.

This

Please Login or Register  to view this content.

can be this

Please Login or Register  to view this content.

But, again, avoid the select.

You can operate on most, if not all, objects (workbooks, worksheets, ranges, cells, Pivot Tables, etc...) without selecting them. But, if you DO select things, your code will be slower.

[jsneak]

That's great thank you.

Would there be a better way of running these lookups though? Would an array be better or maybe not with the amount of data?

[TMS]

INDEX/MATCH is arguably more efficient than VLOOKUP, particularly where you have more than 2 columns.

VLOOKUP should be ok with full column references but it would be better to determine the last row of data and adjust your formulae appropriately.

[TMS]

Oh, and switch ScreenUpdating off and set Calculation to manual before you start processing and reset at the end.

[jsneak]

Hadn't really considered using index & match, would it be the same .formula = ("index/match")?

I might give that a go now, thanks again! I'm going to switch the calc to manual and reset it at the end aswell i think that's the majority of the problem - the calc

[TMS]

Let me know how it goes. If you can provide a sample csv file and a workbook with ALL the current code, I will try and have a look at it.