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.
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.