Nested VBA loop crashes Excel

[daggg]

Public Sub abc123()
Dim EndRow As Long, I As Integer, J As Long, k As Long
I = 1
EndRow = Cells(Rows.Count, I).End(-4162).Row
Application.ScreenUpdating = False
For k = 1 To EndRow
For J = 1 To EndRow
If Cells(k, I + 8) = Cells(J, I) Then
Cells(J, I).Delete
Cells(J, I + 1).Delete
Cells(J, I + 2).Delete
Cells(J, I + 3).Delete
Cells(J, I + 4).Delete
Cells(J, I + 5).Delete
Cells(J, I + 6).Delete
Cells(J, I + 7).Delete
End If
Next
Next
Application.ScreenUpdating = True
End Sub


[FONT=&quot]The macro works on the sample but when I input my data of up to 20,000 rows the spreadsheet freezes and crashes excel.[/FONT]<img style="box-sizing: border-box; vertical-align: middle; border: 0px; color: rgb(73, 73, 73); font-family: "Gotham SSm", Helvetica, Arial, sans-serif; background-color: rgb(250, 250, 250);">

 

[Michael M]

Re: Heeeeelp, please!

Do you actually need to delete the cells or simply clear the contents ???
What are you trying to achieve with this code ??

 

[daggg]

Re: Heeeeelp, please!

Delete them...But I would like to have both options... Let's try first this one... Thank you very much for your answer...

 

[daggg]

Re: Heeeeelp, please!

Sorry, I need to clear the contents... My bad...

 

[Michael M]

ADVERTISEMENT

Re: Heeeeelp, please!

Maybe this then...

Public Sub abc123()
Dim EndRow As Long, I As Integer, J As Long, k As Long
I = 1
EndRow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For k = 1 To EndRow
    For J = 1 To EndRow
        If Cells(k, I + 8) = Cells(J, I) Then
            Range(Cells(J, I), Cells(J, I + 7)).ClearContents
        End If
    Next J
Next k
Application.ScreenUpdating = True
End Sub

 

[daggg]

Re: Heeeeelp, please!

Thank you very much, but again there is that Not Responding error message...

 

[daggg]

ADVERTISEMENT

Re: Heeeeelp, please!

20000 makes the problem...

 

[BQardi]

Re: Heeeeelp, please!

Well, you are looping 20,000 rows 20,000 times. A total of 400,000,000 runs...

 

[daggg]

Re: Heeeeelp, please!

You said it nice...So, what kind of solution do I need here?

 

[Michael M]

Re: Heeeeelp, please!

as asked earlier.....

What are you trying to achieve with this code ??

An explanation of what you are trying to do would be far more helpful, than providing inefficient and buggy code ...