reduce code execution time

  • Hello all,


    the below macro is used to compare the employees project date with the days which are populated in a multiple columns and give a count of how many current assignments an employee is working on a particular day.


    for eg:- if RAnge Q3:Au3 is filled with dates of october 2013,
    like q3:1st oct,r3:2nd oct,s3:3rd oct and so on.
    my code is comparing these individual dates with the employees start and end date from sheet temp calc and returns a count of the no of assignments the employee is working on by counting the employee id. the code works fine but it takes ages for execution(beacuse there are around 50 thousand employees)
    ive then applied filters after i get the data into the sheet in the first place to delete redundant data such as withdrawn,inactive and other employees.also another filter to remove employees that do not fall in my compare range but the employees is still huge and excution time is also large.
    could someone explain how i can reduce the project execution time and any where i can clean up the code for faster execution because the data is only going to increase.


    in case i could not provide enough details i've attached my file in the link below please have a look.


    https://docs.google.com/file/d…EWHYycTg/edit?usp=sharing


  • Re: reduce code execution time


    hello pike,


    i appreciate your suggestion,but this is just a base code i have more stuff that i need to add so i cant do that..


    cheers
    mathew

  • Re: reduce code execution time


    You can with a calx page , dont be afraid of helper columns ect and one display sheet
    Its the only way to go when crunching large amounts of data .. i dont even try with vba its way way way to slow
    the engineer calx and spread sheets only work with spread sheet functions and they a very very complex, iterated ect.. some take 5 seconds with functions and hours with vba and loops

  • Re: reduce code execution time


    I am guessing you have some formulas in your spreadsheet alone. I am sure you noticed this already but you are going to iterate through 26*25*24=15600 times. if it is possible to kill off one of your loops I would suggest you do that. Without seeing the spreadsheet and its exact functionality I do not see a way around this yet. Also if there are any conditions you can put in the first loop it would drastically increase your performance speed, 25*24 iterations that do not have to be performed.
    I would add this to the top of your code.
    Application.Calculation=xlCalculationManual
    and at the end put
    Applicaiton.Calculation=xlCalculationAutomatic

  • Re: reduce code execution time


    hello pike,brewface


    pike as i am reading more about it, array seems to be my best option.but i dont know how to do it using an array. could you help me proceed?
    Brewface, no mate no calculations, but ill keep that in mind:)

  • Re: reduce code execution time


    ohk now,
    ive three macros
    first macro update! gets me data from a file after matching the headers and puts it on dashboard sheet
    second temp allows me to select multiple files through a dialog box and put all of the data in temp calc sheet
    third macro does a very complex thing


    now the question was about macro number 2
    after getting the data i want to remove the blank cells from column number 6 and if data in column 3<n2 and data in column 4 >n1 delete it
    n1 and n2 are used to store date that is taken from dashboard sheet.


    now i use a loop to delete rows that match my condition
    the problem is it takes me 48 mins
    i do not want to spend that much time