VBA: Excel crashes with a bigger amount of data

[Nelson78]

Hello everybody.

I have a problem with one of my macro: with a bigger amount of data then usual, Excel crashes without reporting the type of error.

https://imgur.com/a/gGkwECB

I have isolated the part of the code which causes the matter, and it should be the following.

In this case the amount of data is:
lrt (number of rows) = 2731
populated columns = 32

If I divide the data by 3 (about 1000 rows), the macro works correctly.

Thank you for any suggestion.

Sub add_zero()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

Dim lrt As Long
lrt = Worksheets("Records").Cells(Rows.Count, "A").End(xlUp).Row

For Each cellnum In Range("BF2:BH" & lrt)
If cellnum.Offset(0, 3).Value = "Z" Then
     cellnum.Value = "0" & Trim(Str(cellnum))
End If

Next


    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 

[Nelson78]

Hello everybody.

I have a problem with one of my macro: with a bigger amount of data then usual, Excel crashes without reporting the type of error.

https://imgur.com/a/gGkwECB


I have isolated the part of the code which causes the matter, and it should be the following.

In this case the amount of data is:
lrt (number of rows) = 2731
populated columns = 32

If I divide the data by 3 (about 1000 rows), the macro works correctly.

Thank you for any suggestion.

Sub add_zero()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

Dim lrt As Long
lrt = Worksheets("Records").Cells(Rows.Count, "A").End(xlUp).Row

For Each cellnum In Range("BF2:BH" & lrt)
If cellnum.Offset(0, 3).Value = "Z" Then
     cellnum.Value = "0" & Trim(Str(cellnum))
End If

Next


    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

May be I can proceed with blocks of 100.
Or not?