samedi 18 avril 2015

Excel vba executing crash

I have the following function to run on a large excel ark with 60k rows:



Private Sub mySub()
Dim intRowA As Long
Dim intRowB As Long

Application.ScreenUpdating = False

Range("W1").EntireColumn.Insert

For intRowA = 2 To ActiveSheet.UsedRange.Rows.Count
If Cells(intRowA, 6).Value = "C" Then
For intRowB = 2 To ActiveSheet.UsedRange.Rows.Count
If Cells(intRowB, 6).Value = "P" Then
If Cells(intRowA, 4).Value = Cells(intRowB, 4).Value And Cells(intRowA, 7).Value = Cells(intRowB, 7).Value Then
Cells(intRowA, 23).Value = "Matched"
Cells(intRowB, 23).Value = "Matched"
End If
End If
DoEvents
Next
End If
Next

For intRowA = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
If Cells(intRowA, 23).Value <> "Matched" Then
Rows(intRowA).Delete shift:=xlShiftUp
End If
Next

Range("W1").EntireColumn.Delete

Application.ScreenUpdating = True


End Sub


The idea to check where F columns are C and match them up with all F Rows that are value P Then at the end Delete all that does not match


The problem with this code as far as i can see is that it runs the 60k rows 60K times. which makes my script crash. i am unsure how to improve it and thought that you guys might be able to see through this?


Aucun commentaire:

Enregistrer un commentaire