Best performance option for helper cells

Sorry if it is a bit obscure. I'm pretty good and VBA and Excel but I am a bit stumped as to best practice on the following problem. I wondered if any of my wonderful friends could lend a hand.

I am building some upgraded dashboards. My old ones are pretty fast even at a 50mb file size.

I have used helper rows for set time periods and other true false static data. I then use =Sumifs(Total,Range1,True) for example.

There is a VBA macro that pulls everything in and assigns the static true false data to the columns needed.

What I want to know is what is faster or best practice for these helper columns.

Should I go with 0 for false and 1 for true.

I have a range and a variant array to pop them in then run conditional IF or other code and then deposit the result back in the sheet so I can make pretty charts and graphs.

Please Login or Register  to view this content.

Or is it better to go with

Please Login or Register  to view this content.

Or is it better to go with

Please Login or Register  to view this content.

There is then code to paste the array into the cell. i.e. rng = Arr

Any ideas as to what would speed up the formulas best?

Thanks in advance if anyone knows.

Don't use text values (your 3rd option). Don't use variant arrays. IF can be quite slow if you have a lot of calculations.

Your second option is likely to be faster, and will occupy less memory, as Boolean values (True and False) only use a single byte (I think), whereas numerics in your first option will use more bytes. Define the array as Boolean rather than Variant. Also, you might like to write the code like this:

Please Login or Register  to view this content.

which will return True or False directly, as appropriate.

If your array is not very big, though, it won't make a lot of difference between Option 1 and Option 2.

Hope this helps.

Pete

Sorry I was not clear, I have been trying to explain this to my boss all day but my lack of the English language is letting me down.

I use variants to load a range of values from a sheet in one go without "redim preserve". I load the whole sheet into memory then do 100 and 1 things with it 1.2 seconds rather than reading and writing cells 30 -60 seconds.

The sheet contains columns with times, dates, distances, and other customer category data. Say 1000000 rows with 26 columns. 20 of which are used. the 6 columns at the end are in the array as blank, I then set them to true or false.

Say I use a work book function on the time likes =CountIfs(Times,">="&0500,Times,"<"&0700).

That row will always be in that time bracket, so its true or false. so then I just need to say =Countifs(timeHelper,true). It is so fast. I just want to know is it better to have Typed true false in the cell, true false with an = or 0 - 1.

i.e.

Please Login or Register  to view this content.

you put it back in the work book by

Please Login or Register  to view this content.

loads the range in one go, you have to use variant for arrays for this method or you get errors.

otherwise its load every cell one by one and that takes time. like

Please Login or Register  to view this content.

I'm just looking for the fastest option for the final workbook function with countifs, sumifs and averageifs. I use in cell formulas at the moment but I don't want the book clogged up formulas for static helper columns.

I will however use your vba to speed up the data import. looks very good.