Macro randomly fails; possibly with large volume of line items

[cdrobinson83]

Hello,

So I created this macro to sift through data and produce some weekly metrics. It works if I only do 1 week at a time (5 days) which is generally around 1,700 lines of raw data. It runs quickly and smoothly. But if I try for more data, it chokes and freezes without an error message. It seems strange that it would fail since it runs quickly in other cases. So I'm wondering if it's not actually related to the number of items to sift through.

 

[cdrobinson83]

Sub CLEANUP()



'

' Delete Montreal equities that are not from early exercise

'



'

Sheets("COPY").Select

Range("A1").Select



ActiveSheet.Range("$A$1:$Z$10000").RemoveDuplicates Columns:=Array(3, 7, 8, 9, 10, _

13, 15, 19), Header:=xlYes

Range("A1").Select



Columns("T:T").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("T1").Select

ActiveCell.FormulaR1C1 = "EARLY"

Range("A1").Select



R = 2

C = 1



Do Until Cells(R, C) = ""



If Range("O" & R) = "1" And Range("N" & R) > Range("S" & R) Then

Cells(R, C + 19) = "Y"

Else

Cells(R, C + 19) = "N"



End If



If Range("H" & R) = "CB" And Range("O" & R) = "1" Then

Cells(R, C).EntireRow.Delete

ElseIf Range("H" & R) = "CB" And Range("N" & R) < Range("S" & R) Then

Cells(R, C).EntireRow.Delete

ElseIf Range("H" & R) = "CB" And Range("O" & R) = "2" Then

Cells(R, C + 19) = "Y"

Else

R = R + 1



End If



Loop



Sheets("COPY").Select

Cells.Select

Selection.Copy

Sheets("PASTE").Select

ActiveSheet.Paste

Range("A1").Select



ActiveWorkbook.RefreshAll



Sheets("PASTE").Select

Range("A1").Select

Sheets("COPY").Select

Range("A1").Select

Sheets("MACRO").Select

Range("A1").Select





End Sub

 

[jolivanes]

Maybe if you explain in a concise manner what you want to achieve, someone will show you a code that works.

BTW, selecting is very seldom required, the last 13 lines you have it 10 times, and slows down computing tremendously.

 

[cdrobinson83]

I was able to figure it out. Part of it was some of the criteria clashing with other instances. So I was able to amend the actual raw data I was consuming to eliminate some of the unwanted records to start out with. After that, I was able to eliminate the processing error by just tweaking the original steps. I also removed many of the “select” commands as you pointed out