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.
Thanks!
Please Login or Register to view this content.
Well, you should avoid selecting anything. For example, thiscan be thisPlease 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.Please Login or Register to view this content.
This
can be thisPlease Login or Register to view this content.
But, again, avoid the select.Please Login or Register to view this content.
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.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
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?
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.
Oh, and switch ScreenUpdating off and set Calculation to manual before you start processing and reset at the end.
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
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks