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

 

[Rick Rothstein]

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.

I think the "Not responding" message comes from the Windows indicating it is not "hearing back" from Excel when it does its internal check (I do not believe it is tied to the number rows matched or not matched). This can happen with any program tied up in lengthy, tight looping code. I could make those message go away by including a DoEvents statement that would be executed every so often (maybe every 50 or maybe 100 iterations), but that would tend to slow the program up a little more. Just out of curiosity, about how long is it taking for my code to execute?

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).

I was not aware this... I thought the entire address was case insensitive.

 

[daggg]

Re: Heeeeelp, please!

No, you don't have to include a DoEvents statetment, it's all good...
It takes less than 30 sec for your code to execute. Which is really fast, I must admit...

Yes, as we can see it, it's case sensitive...So, just to have that option also covered, and if it's not too much trouble for you, please edit your macro...

Thanks.

 

[daggg]

Re: Heeeeelp, please!

And, one more situation, as you mentioned here earlier...
What about this...
The data column i and column a do not have to be in the same row. For instance is john@gmail.com is in i25 the macro would need to search the entire column a and might find it in a72 then a7th thru h72 would be deleted. So, the I25 stays only...

Thank you.

 

[Macropod]

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).

I was not aware this... I thought the entire address was case insensitive.

The Simple Mail Transfer Protocol (RFC 5321) provides, at Section 2.4:
The local-part of a mailbox MUST BE treated as case sensitive. Therefore, SMTP implementations MUST take care to preserve the case of mailbox local-parts. In particular, for some hosts, the user "smith" is different from the user "Smith". However, exploiting the case sensitivity of mailbox local-parts impedes interoperability and is discouraged. Mailbox domains follow normal DNS rules and are hence not case sensitive.

In practice, however, web-based and Windows-based servers and services typically don't enforce the standard. Consequently, whichever way you go with case-testing of email addresses, you're liable to end up with the wrong results. Not applying case-sensitivity may result in records being deleted when they shouldn't be - or wrong records being deleted and other (corresponding) wrong records being retained. Conversely, applying case-sensitivity may result in records being retained for no other reason than that the same email address has been stored in a variety of formats. And that is so even if one codes around the case-insensitivity of domain names.