Too heavy and slow.

I usually don't have to post on any forum to find an anwser but i'm really tired of looking for a solution for this particulary problem.

So, the problem is that the "table" e39:ai48 is slowing everything.
When i change dates, this table "change" shape, but its taking too long to do it.
I also have a macro buttom on the top tha simply hide and unhide some rows, but after i've finished this "table" it's taking too long to do it.

The formula in the cells of this table look for diffrent types of "service" that every "user (usuário)" have completed/done.
In other words, every countinfs inside the cell is a kind of service the person have finished and every one of them have differents wheigts, the objective here is to find the amout of "proportional tasks completed" by day for each user.

So far, i've tried to change the formula and the macro for the "hide/unhide" problem.

File attached, I reduced the data to 5k row, but it usually has 50k+ rows.
Sorry for my bad english.
Thkz.

That's a pretty complex workbook. You'd see an improvement in performance if you declared your ranges instead of searching every row.

G:G = 1,048,576 rows
G3:G60003 = 60,000 rows

Your formulas are covering a lot more cells than is necessary.

Thkz, i'll try that, but the number of the rows can change, basically every time someone updates the data table.

I'll think something out, powerpivot would help?
I'm new at powerpivot but i think I can handle it, but i'm not sure it would solve the problem =/

You can always just overestimate to include . Even if you only target 100,000 rows, that's still only 10% of what you're processing now.

I've never given PowerPivot a shot, but pivot tables in general run faster than creating the formulas by hand.

I'd definitely give the VBA Forums a shot!

There's some real solid programmers on the site that know their stuff.

My VBA experience is pretty weak; the best I can manage so far is For Each loops.

Without knowing what you are doing, it looks like you are checking to see the results of a calc, then returning an answer based onthat calc. In column F (at least), you repeat the same calc twice...

Formula:

Please Login or Register  to view this content.

Perhaps, instead of keep repeating ther formula, put the calc in a helper and then refernce the helper. Not only nmay that speed things up, it will also make editing/trouble-shooting a LOT easier
Adjusting ranges may help, as daffodil pointed out