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