Macro crashing due to "out of Memory"

[karmaimages]

I have the following macro in excel:

Sub Analyze()
'   , ' ; - !
LR = Application.WorksheetFunction.CountA(Sheets("Address Original").Range("A:A"))
k = 2
Sheets("Errors").Range("A2:N1000000").ClearContents
For i = 2 To LR
c = ""
For j = 1 To 13
c1 = 0
c2 = 0
c3 = 0
c4 = 0
c5 = 0
c6 = 0
c7 = 0
c8 = 0
c9 = 0
c1 = InStr(Sheets("Address Original").Cells(i, j), ",")
c2 = InStr(Sheets("Address Original").Cells(i, j), "'")
c3 = InStr(Sheets("Address Original").Cells(i, j), ";")
c4 = InStr(Sheets("Address Original").Cells(i, j), "-")
c5 = InStr(Sheets("Address Original").Cells(i, j), "!")
c6 = InStr(Sheets("Address Original").Cells(i, j), "&")
c7 = InStr(Sheets("Address Original").Cells(i, j), "/")
c8 = InStr(Sheets("Address Original").Cells(i, j), "\")
c8 = InStr(Sheets("Address Original").Cells(i, j), "*")
If c1 > 0 Then c = c & ","
If c2 > 0 Then c = c & "'"
If c3 > 0 Then c = c & ";"
If c4 > 0 Then c = c & "-"
If c5 > 0 Then c = c & "!"
If c6 > 0 Then c = c & "&"
If c7 > 0 Then c = c & "/"
If c8 > 0 Then c = c & "\"
If c9 > 0 Then c = c & "*"
Next j
If c <> "" Then
Sheets("Errors").Rows(k).Value = Sheets("Address Original").Rows(i).Value
Sheets("Errors").Range("N" & k).Value = c
k = k + 1
Sheets("Address Original").Rows(i).Delete
End If
Next i

'
End Sub

It's analysing around 500,000 rows of data in excel, but it's causing excel to crash as it runs out of memory. Does anyone have any suggestions on how to stop this happening?

 

[NdNoviceHlp]

Put Option Explicit at the top. I'm guessing that it's the deleting rows that's causing the crash. HTH. Dave

 

[karmaimages]

Put Option Explicit at the top. I'm guessing that it's the deleting rows that's causing the crash. HTH. Dave

Thanks for the reply, when I add OE, it now errors with a compile error, Variable not defined and LR is highlighted.

 

[Fluff]

Option Explicit, simply forces you to declare all your variables & is strongly recommended.
As for your problem, there could be a number of reasons.
The best thing, would be for you to explain what you are trying to achieve. That way, one of the members here, may be able to come up with a solution that works.

 

[NdNoviceHlp]

I'm guessing if U turn off the auto document recovery feature that your macro will stop crashing. Also consider turning off screen updating and automatic calculations at the start of the code and then turning them back on at the end of your code. This will greatly reduce the time it takes to run your code and/or stop the crashes. Good luck. Dave

 

[karmaimages]

I'm guessing if U turn off the auto document recovery feature that your macro will stop crashing. Also consider turning off screen updating and automatic calculations at the start of the code and then turning them back on at the end of your code. This will greatly reduce the time it takes to run your code and/or stop the crashes. Good luck. Dave

Hi Dave,

Unfortunately not it still crashed when it gets to just over 1gb of ram. Do you think there is a better way to have this macro run.

It's searching for errors in addresses that contain the characters above then moving the row if any of them appear and putting the characters it finds in column N on the error sheet.

 

[NdNoviceHlp]

Maybe start at the beginning.... what is it that U are actually trying to accomplish? Dave