+ Reply to Thread
Results 1 to 3 of 3

Process / speed issue on larger formulas - needs optimizing

  1. #1
    Registered User
    Join Date
    05-02-2020
    Location
    Vancouver, Canada
    MS-Off Ver
    Office 2019
    Posts
    68

    Question Process / speed issue on larger formulas - needs optimizing

    Hello, hoping to find some direction to optimize formulas. The workbook has about 15 sheets all interconnected (I'm not sure how to make a sample section to post here without wrecking it all).
    This is for work; it's a system to drop in a footage takeoff from an estimating program in construction, which the excel then reads the codes and aligns them with assemblies which are built from a materials list.
    It was working fine until I took the equation in the columns Mat.1 & Mat.1 Value through to Mat.12. and filled down 500 rows.

    In the picture, the # next to "Levels" is the floor level pulled from another page (there's 84 levels in the template). There's VBA programming that will filter through the 'master!' sheet on which level is selected and then hide the rows in this sheet that are not relevant to that floor. Now that I've filled down, the VBA looks like it's lagging very shortly after it starts. Now takes about 45 seconds to finish (instead of the 2 seconds it did when I only had these equations in a couple dozen rows).

    There's 12 Mat(erial) columns & 12 Value columns, filled down 500 rows:

    excelhelp1.jpg

    Mat.1:
    =IF(INDIRECT("'Master'!"&A$1&(ROW()))="","",IFERROR(IF(INDEX(Assemblies!$I$2:$I$500,MATCH($E4,Assemblies!$C$2:$C$500,0))=0,"",INDEX(Assemblies!$I$2:$I$500,MATCH($E4,Assemblies!$C$2:$C$500,0))),""))

    Mat.1.Value:
    =IFERROR(IF($C4="Dimension",INDEX(Assemblies!$E$2:$E$500, MATCH($E4,Assemblies!$C$2:$C$500,0)),INDEX(Assemblies!$L$2:$L$500, MATCH($E4,Assemblies!$C$2:$C$500,0))),"")

    The above equations reference another sheet to which has an equation just as sad... and so on.

    Upon changing the level, the VBA is supposed to (and works with less rows with equations) simply hide the rows that don't have anything in all of the Mat.1 through Mat.12 columns.
    This image is about 10 seconds in:

    excelhelp2.jpg

    When it's finally finished, it's left a pile of rows that have blank lines all over; seems quite random as it's not the same every time. If it didn't mess up and leave the blank rows, I might consider living with the poor equations on my part; but she doesn't work properly.

    Any direction would be grand; or help to fix my sad equations.

    Edit: perhaps it's the VBA that's assisting in the slowness (runs when the Level is changed):
    Please Login or Register  to view this content.
    Last edited by purgamentum; 05-02-2020 at 10:06 PM.

  2. #2
    Registered User
    Join Date
    05-02-2020
    Location
    Vancouver, Canada
    MS-Off Ver
    Office 2019
    Posts
    68

    Re: Process / speed issue on larger formulas - needs optimizing

    FYI: Changed the VBA to:
    Please Login or Register  to view this content.
    The stuttering or 'lag' is gone; it slowly clears all the rows as intended, it's rather slow still but a little better than before.

    If I test by stopping the filtering (comment out the VBA); then change the level, all the cells with material/prices near immediately update to the new level data. I suppose that's what I'm hoping is that there'd be some code for filtering that would hide the blanks in 1-2 seconds vs doing loops around checking every one as it recalculates. (if that makes sense)
    Last edited by purgamentum; 05-03-2020 at 12:41 AM.

  3. #3
    Registered User
    Join Date
    05-02-2020
    Location
    Vancouver, Canada
    MS-Off Ver
    Office 2019
    Posts
    68

    Re: Process / speed issue on larger formulas - needs optimizing

    FYI: ended up making a helper column at the end of the sheet with:

    =COUNTIF(G4:AD4,"")

    filled down to produce a code 24 on the blank cells which then filtered in the VBA:
    Please Login or Register  to view this content.
    Guess I don't need the And ^ in there either.

    10x faster at least

+ 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. Feedback / ideas for optimizing this VBA code for speed
    By piedpiper11 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-11-2013, 06:09 PM
  2. Vlookup and Calculating Issue - Please help me to speed this process up.
    By lizb72 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-10-2013, 06:07 PM
  3. [SOLVED] Optimizing code speed: Hiding rows with loops - looking for faster way
    By Taktiker in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-07-2012, 04:24 PM
  4. Need to speed up process...
    By y34r1ght in forum Excel General
    Replies: 13
    Last Post: 01-10-2007, 06:17 PM
  5. VB code - To Speed up process
    By test1986 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-24-2006, 11:22 AM
  6. Optimizing memory, speed: Arrays vs. Cells, etc...
    By T_o_n_y in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-02-2006, 04:15 PM
  7. [SOLVED] Speed up Program Process
    By maperalia in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-26-2006, 04:00 PM
  8. [SOLVED] Speed up the process
    By Ali Baba in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-03-2005, 02: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