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);">

 

[daggg]

Re: Heeeeelp, please!

I'm so sorry, you are absolutely right, for the last few days I'm having fever, high temperature etc., so I just read it fast and it's my mistake...

"To be clear, a value in, say, cell I3 could match a value in, say, cell A123 and, if so, A123:H123 should be cleared (not deleted), correct?" No, only if I3 is equal to A3 then that row must be deleted.

"If so, will the matches, when they occur, be exact or could the letter casing of one be different than the letter casing of the other?" Yes, letter casing of one can be different that letter casing of the other.

Once again, thank you.

 

[Rick Rothstein]

Re: Heeeeelp, please!

I'm so sorry, you are absolutely right, for the last few days I'm having fever, high temperature etc., so I just read it fast and it's my mistake...

"To be clear, a value in, say, cell I3 could match a value in, say, cell A123 and, if so, A123:H123 should be cleared (not deleted), correct?" No, only if I3 is equal to A3 then that row must be deleted.

"If so, will the matches, when they occur, be exact or could the letter casing of one be different than the letter casing of the other?" Yes, letter casing of one can be different that letter casing of the other.

I assumed the highlighted text means delete the entire row, Column I included, not just Columns A:H only. Given that, give this macro a try...

Sub ClearAthruHifImatchesA()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "I").End(xlUp).Row
  With Range("A1:A" & LastRow)
    .Value = Evaluate(Replace("IF(A1:A#=I1:I#,"""",A1:A#)", "#", LastRow))
    On Error Resume Next
    .SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0
  End With
End Sub

 

[Rick Rothstein]

Re: Heeeeelp, please!

I assumed the highlighted text means delete the entire row, Column I included, not just Columns A:H only. Given that, give this macro a try...

Sub ClearAthruHifImatchesA()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "I").End(xlUp).Row
  With Range("A1:A" & LastRow)
    .Value = Evaluate(Replace("IF(A1:A#=I1:I#,"""",A1:A#)", "#", LastRow))
    On Error Resume Next
    .SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0
  End With
End Sub

Let's stop the screen updating so that everything appears to happen at the same time (should also speed the macro up slightly as well)...

Sub ClearAthruHifImatchesA()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "I").End(xlUp).Row
  [COLOR="#0000FF"]Application.ScreenUpdating = False[/COLOR]
  With Range("A1:A" & LastRow)
    .Value = Evaluate(Replace("IF(A1:A#=I1:I#,"""",A1:A#)", "#", LastRow))
    On Error Resume Next
    .SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0
  End With
  [COLOR="#0000FF"]Application.ScreenUpdating = True[/COLOR]
End Sub

 

[daggg]

Re: Heeeeelp, please!

Thank you so much for the fast reply and help...
I'll test it now.

 

[Rick Rothstein]

Re: Heeeeelp, please!

Thank you so much for the fast reply and help...
I'll test it now.

That was 9 hours ago... is my code still running?

 

[Michael M]

Re: Heeeeelp, please!

The OP may have stopped for a Xmas drink.....

 

[daggg]

Re: Heeeeelp, please!

No, no drinks for me...

Rick Rothstein
Thank you very much, I get somewhere around 12k Not Responding notification, but the macro is great, and it does the job...

If the matches, when they occur, must be exact...? John@gmail.com in A55 must match Jahn@gmail.com in I55?

Is this too much mo edit your code?

Once again, thank you.

 

[Rick Rothstein]

Re: Heeeeelp, please!

No, no drinks for me...

Rick Rothstein
Thank you very much, I get somewhere around 12k Not Responding notification, but the macro is great, and it does the job...

What do you mean by "Not Responding" notifications? Are theses text in the cells? The only place I can think of for that message is in the Excel title bar, but if that is where it is occurring, I cannot think of a way you would know there were 12,000 of them. Can you clarify this for me?

If the matches, when they occur, must be exact...? John@gmail.com in A55 must match Jahn@gmail.com in I55?

So you are saying that John@gmail.com should not match john@gmail.com even though email programs see them both as being the same?

 

[daggg]

Re: Heeeeelp, please!

Yes, you are absolutely right, "Not responding" is in the Excel title bar.
I have 12k of rows with various data in the cells from A to I... Somewhere there is a match, and somewhere there isn't... So, I use that data to test your code.

Yes, John@gmail.com should not match john@gmail.com. I don't believe that email programs see them both as being the same...
Thanks.

 

[daggg]

Re: Heeeeelp, please!

The domain name part of an email address is case insensitive (i.e. case does not matter). The local mailbox part, however, is case sensitive. The email address ReCipiENt@eXaMPle.cOm is indeed different from recipient@example.com (but it the same as ReCipiENt@example.com).