Excel VBA not responding when running a macro with a loop

[soula1998]

Hello community, this is my first post after endless attempt for 2 weeks straight.

So I have these mega Excel files, with almost 600 000 lines.
I have to calculate the average of certain values in my sheet (under certain conditions), while looping through a column (second one).
I have to loop my program with a while loop for at least 630 000 times.
Except when I launch the macro, it automatically says: Excel does not respond .. and it stays like this for hours. The longest period of time I let it run was 11 hours without touching anything... I was still not responding ...
Funny thing is: when I reduce the amount of looping just to test, it is extremely fast when I do it for 10 000 times but it gets blocked again around 35 000.

Here is my code:

Dim rw As Long
Dim erw As Long
rw = 3
erw = 631000
Dim r As Long
Dim i As Long
Dim somme As Long
For r = 1 To 200
i = 0
somme = 0
Do While rw < erw
If IsNumeric(Cells(rw, 7)) = True Then
If Cells(rw, 2) = r And Cells(rw, 7) <> 0 Then
c = Cells(rw, 7).Value
somme = somme + c
i = i + 1
End If
rw = rw + 1
End If
Loop
If i <> 0 Then
Cells(r + 2, 8).Value = somme / i
End If
Next



End Sub

I did try to add the following after suggestions in this site:

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Application.Calculation = xlAutomatic
ThisWorkbook.Date1904 = False
ActiveWindow.View = xlNormalView

But it still was not working. If anyone can help me with it, I'd be extremely grateful. This is my first VBA code, and I know I can do it without a Macro, but my boss insists on me doing it this way...

Thank you so much.

 

[offthelip]

One of the main reasons that Vba is slow is the time taken to access the worksheet from VBa is a relatively long time.
To speed up vba the easiest way is to minimise the number of accesses to the worksheet. What is interesting is that the time taken to access a single cell on the worksheet in vba is almost identical as the time taken to access a large range if it is done in one action.

So instead of writing a loop which loops down a range copying one row at a time which will take along time if you have got 50000 rows it is much quicker to load the 50000 lines into a variant array ( one worksheet access), then copy the lines to a variant array and then write the array back to the worksheet, ( one worksheet access for each search that you are doing),


I have a simple rule for fast VBA: NEVER ACCESS THE WORKSHEET IN A LOOP.
I have modified your code to load all the data into a variant array (inarr) and then used a second variant array (outarr) to write the results into. I ahven't tested this code but it should be at least 1000 times faster, ( I really mean at at least a 1000 times faster)

Sub test()

Dim rw As Long
Dim erw As Long
rw = 3
erw = 631000
Dim r As Long
Dim i As Long
Dim somme As Long
inarr = Range(Cells(1, 1), Cells(erw, 7))
outarr = Range(Cells(1, 8), Cells(erw, 8))
For r = 1 To 200
i = 0
somme = 0
For rw = 3 To erw
    If IsNumeric(inarr(rw, 7)) = True Then
        If inarr(rw, 2) = r And inarr(rw, 7) <> 0 Then
        c = inarr(rw, 7)
        somme = somme + c
        i = i + 1
        End If
'        rw = rw + 1
    End If
Next rw
If i <> 0 Then
outarr(r + 2, 1) = somme / i
End If
Next r
Range(Cells(1, 8), Cells(erw, 8)) = outarr


End Sub