+ Reply to Thread
Results 1 to 8 of 8

Too heavy and slow.

  1. #1
    Registered User
    Join Date
    02-02-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    33

    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.
    Attached Files Attached Files
    Last edited by heuder; 02-24-2014 at 03:15 PM.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Too heavy and slow.

    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.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    02-02-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Too heavy and slow.

    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 =/
    Last edited by heuder; 02-24-2014 at 03:49 PM.

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Too heavy and slow.

    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.

  5. #5
    Registered User
    Join Date
    02-02-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Too heavy and slow.

    daffodil11, insted of fixing na range, i've used na indirect formula so the formulas will look as long it has data, that way I can update the data table and the range will algo change.
    I did the calculation of the "range" on other cells and inside the formula I used that 9 or 10 cells as reference, to avoid unecessery process.

    I've never worked with large data before and so I never had this king of problems before, you helped me understand a thing that I never thought about before.

    Now, i know there is a better way to write the macro code to hide and unhide rows. I've founded on the net, but I think my level of knowledge wasn't enough to understand it =/

    Should I create a new thread in the vba section? oO

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Too heavy and slow.

    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.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,316

    Re: Too heavy and slow.

    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: copy to clipboard
    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Registered User
    Join Date
    02-02-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Too heavy and slow.

    Yeah, I already done that.
    I don't want the "zeros" to show up, so I removed the IF function and used an conditional formatting to hide zeros.

    It still taking some time to process but it's better and tolerable.
    Thkz, I'll check this as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. File has become very heavy
    By Suraj3825 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-06-2013, 02:49 PM
  2. File size became Heavy
    By abhijit786 in forum Excel General
    Replies: 1
    Last Post: 01-02-2011, 07:46 AM
  3. Excel slow at loading text heavy workbooks
    By kieranmoore_uk@yahoo.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2006, 07:55 AM
  4. Lookup from a very heavy file
    By Hari in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-23-2006, 02:35 AM
  5. Heavy/Slow Calculations
    By Shawn in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-16-2005, 01:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1