PDA

View Full Version : Excel crashing every time I run my macro



wlederer
06-18-2014, 02:46 PM
Hi All,

Every time I run my macro it crashes excel. The code is designed to delete records from a range if it is a duplicate (based on 2 cells in the same row). The headers for the cell are in Row 3, and the criteria I'd like to match on are in columns C and E. Below is the code:


Sub Delete_Duplicate_Orders()
Dim GlbOrg As String
Dim OrderNum As String
Dim SelectionForEvalCount As Double
Dim DeletingActionCount As Double
Dim i As Single
Dim x As Single
Dim GlbOrg2 As String
Dim OrderNum2 As String
SelectionForEvalCount = Range(Range("C4"), Range("C4").End(xlDown)).Rows.Count
For i = 1 To SelectionForEvalCount
OrderNum = Range("E3").Offset(i, 0)
GlbOrg = Range("C3").Offset(i, 0)
DeletingActionCount = Range(Range("C3").Offset(i, 0), Range("C3").Offset(i, 0).End(xlDown)).Rows.Count
For x = 2 To DeletingActionCount
OrderNum2 = Range("E3").Offset(x, 0)
GlbOrg2 = Range("C3").Offset(x, 0)
If OrderNum2 = OrderNum And GlbOrg2 = GlbOrg Then
Range("E3").Offset(x, 0).EntireRow.Delete
End If
GlbOrg2 = " "
OrderNum2 = " "
DeletingActionCount = DeletingActionCount - 1
Next x
Next i
End Sub

I am using excel 2010 on a pc.

Thanks in advance,

p45cal
06-18-2014, 04:20 PM
DeletingActionCount at some point is getting to the number of rows on the entire sheet. So it's taking a long time, perhaps it may never finish.
Try this one liner:

Sub Delete_Duplicate_Orders2()
Range(Range("C4"), Range("C4").End(xlDown)).EntireRow.RemoveDuplicates Columns:=Array(3, 5), Header:=xlNo
End Sub