Newbie here. Macro functions but extremely slow

**nkagar2** · 09-29-2014, 12:51 PM

Hello All!

I have had so much help with these forums and was wondering if someone could help me with my macro. I am a civil engineer with no programming knowledge. Most of the vba macro I created is by using codes from this forum or by recording in excel and extending it a little further. It has worked so far however it takes hours and hours to process all the files. I now have a folder with over 600 files that I have to process and I have let it run overnight and it has successfully however I cannot present it to the users since it is time consuming. Couple of specific issues that I as a non-programmer can mention is:

1. Module 37 is the master list of all the macro I use --- I dont think this has any issues
2. Call one is pretty straight forward - it imports all the csv like files into the current workbook -- Don't think this has any issues
3. Call two (module 6) - calculates average speed and uniformity metrics. --- This is the one I think has major issues. Since the data is variable, I did not know how to mention "check for rows with values and calculate average". Instead, I calculate average of full columns or sometimes specific cell numbers which in this case I set it as 3600 rows. Then all the calculations are done cell by cell since I recorded macro and generalized. This macro takes a ton of time.

4. Module 29 also is recorded and is time consuming.

5. Module 33 is also recorded and takes time to copy and paste formula cell by cell.

I was wondering if anyone out here could help/ guide? I have attached the macro for your reference. Thanks in advance!

Sarah

**Norie** · 09-29-2014, 01:07 PM

First thing you should do is get rid of all the unneeded activating and selecting.

For example, this gets rid of all the selecting/activating in the sub Two_Avg_Speed_Loop.

Sub Two_Avg_Speed_Loop()
Dim ws As Worksheet

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For Each ws In ActiveWorkbook.Worksheets

        If ws.Name Like "*.CSV" Then
        
            With ws
            
                Set range1 = .Range("D:D")
                Set range2 = .Range("O:O")
                Set range3 = .Range("Q:Q")
                Set range4 = .Range("S:S")
                Set range5 = .Range("J:J")
                
                .Range("N1") = WorksheetFunction.Average(range1)
                .Range("O1:O3600").FormulaR1C1 = "=IF(RC[-11]="""","""",ABS(RC[-11]-R1C14))"

                .Range("P1") = WorksheetFunction.Average(range2)
                .Range("Q1:Q3600").FormulaR1C1 = "=IF(RC[-2]="""","""",(RC[-2]/R1C14))"

                .Range("R1") = WorksheetFunction.Average(range3)
                .Range("S1:S3600").FormulaR1C1 = "=IF(RC[-2]="""","""",ABS(1-RC[-2]))"

                .Range("T1") = WorksheetFunction.Average(range4)

                'percent green calculations

                .Range("U1").Value = "1"

                .Range("U2:U3600").FormulaR1C1 = "=IF(RC[-8]="""",R[-1]C,1+R[-1]C)"

                .Range("V1:V3600").Fomula = "=D1"

                .Range("W1").Value = "1"

                .Range("W2:W20").FormulaR1C1 = "=R[-1]C+1"

                .Range("X1:X3600").FormulaArray = _
                "=MIN(IF((R1C21:R3600C21=RC[-1])*(R1C22:R3600C22<100),R1C22:R3600C22))"

                .Range("Y1").FormulaR1C1 = "=COUNTA(C[-12])"

                .Range("Z1").FormulaR1C1 = "=RC[-1]+1"

                .Range("AA1:AA3600").FormulaR1C1 = "=IF(RC[-4]="""","""",IF(RC[-4]>R1C26,"""",RC[-4]))"

                .Range("AB1:AB3600").FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-4])"

                .Range("AC1").FormulaR1C1 = "=COUNTIF(C[-1],0)"

                .Range("AD1").FormulaR1C1 = "=RC[-5]-RC[-1]"

                .Range("AE1").FormulaR1C1 = "=RC[-1]/RC[-6]"

                .Range("AE1").NumberFormat = "0.00"
             
                .Range("AF1") = WorksheetFunction.max(range5)

            End With

        End If
    Next ws
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

That should speed things up a bit, but I'm sure there are other things that can help.

One thing I did notice was the array formula you are putting in 3,600 cells in column X, that's bound to take up some processing time.

PS You might consider turning off calculations when placing the formulas in the cells.

**nkagar2** · 09-29-2014, 01:16 PM

That was super quick. thanks! I will try that and see if it speeds up. I am not sure if I have to hire a pro to rewrite the full code because processing 77 files took 28 minutes. And some folders have over 600 files!

**nkagar2** · 09-29-2014, 01:44 PM

Thanks, Norie! It did help to an extent. I hope I can make similar adjustments to other macros to speed it up further. Thanks! Any other pointers, anyone ????

**Norie** · 09-29-2014, 02:35 PM

I think to give any further specific help it might involve seeing a sample of the data you are working with.

**nkagar2** · 09-29-2014, 02:41 PM

Hi, Norie! Thanks for the note. I could not attach a file in the reply so here is the link to the sample files.

dropbox.com/s/sxkrlhmtsxopnev/NE.zip?dl=0 In the main tab I input the path to the (extracted data) folder and the macro should run. Thanks again for your time and help!

**nkagar2** · 09-29-2014, 02:43 PM

Here are the cross post links at other places:

mrexcel.com/forum/excel-q...ent-macro.html
excelforum.com/excel-prog...mely-slow.html
ozgrid.com/forum/showthread.php?t=191169