To improve code speed - drill down options on excel

  • Hi All,

    Great forum with plenty of help and thank you all for being active as your input is quite often valuable and useful. It came a time when I thought to get your view on my work and some performance problem I deal with i.e. time of macro being run.
    I developed a sheet that shows top level values and when pointing on '+' sign it drills into details of that value (while in reality it does execute another macro to provide details - based on advanced filtering) all runs ok no issues but due to data size it is a little slower than I would expect i.e. on average 14 seconds per drill down. I have tried to streamline code where possible and also switched off additional functions when macro run but it resulted in improvement from 17 seconds to 13 seconds only. Would that be max I can get from it. Or is there anything else I could improve to get to a better execution time. To note there are just over 20k rows to deal with at any time from the data I filter.


    To support it see first code for calling '+' action and then below second for getting into drilling mode


    Second part to execute ShowTopLevel macro


    Please advise what I can do extra to make it better.

    Really appreciate it.

  • Hi, welcome to Ozgrid!


    When posting code you must place it in a "Code box". To do so, copy your code, then click the "</>" icon in the reply box menu bar, and paste code into the code box that will appear. I have done this for you in this instance.


    As for your code.


    Using "If......ElseIf.......End If" statements is far more than separate "If.....End If" statements even with End in each If statement, but better still is "Select Case.....Case....End Select"


    I have modified your first code to use Select case



    You can parse the required row into your other two codes by using Target.Row so there is no need to use ActRow = ActiveCell.Row.


    I have done that and improved your "With.....End With" statements. Further improvements could well be possible but I would need to see your workbook to know for sure.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • KjBox,


    thank you for info. Some useful additions especially around 'Case', certain order might not work with the overall workbook especially on manualcalc.

    Initially tested but didn't work so need to verify it again. Too late today to think as was sitting on it from morning.


    with end statement - spot on. I missed it somehow.

  • I couldn't leave it so just grabbed a drink and got through case option - all works and squeezed the time in half (I didn't expect it to be so long for the initial call function). But I need to leave Target.Row for tomorrow as getting error again on call macro somehow or it doesn't call it at all

  • Further note on Target.Row - no real improvement on macro timing on this occasion.

    Overall after adding all data rows (21k rows) the time is just below 10 seconds overall. Not the best but definitively better than 17 seconds previously. 'Case' instead of 'If' option made a trick. I would need to rethink on the data itself to see what else can be done (perhaps through sql on data before hand)

  • Didn't really expect Target.Row to make a difference in speed, it was just a better way of coding.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • If you are importing data from an external source then Power Query would be even better than SQL

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.