Excel VBA simple if not statement

Question

I am trying to run a relatively simple loop to pull out values I don't want from a list.

Everything below is working correctly other than my "Not" statement which does not create an error...

it just doesn't seem to do anything at all.

Set myRange = Range("A49:A150")
For Each myCell In myRange
    If myCell Like "*msn.com*" Or _
        myCell Like "*messaging.microsoft.com*" Or _
        Not myCell Like "*.com*" Then
        myCell.Delete
    End If
Next myCell

I am fairly certain I have a simple mistake in here somewhere but I have been unable to find it.

Answer 1

Its not the Not statement. Your issue is that when you delete the cell, you range changes and so your loop is missing data. If Cell A49 is deleted then the value at A50 moves up to A49 and is never checked. Aggregate your cells which qualify to get deleted and then delete them in one go.

Sub test()

    Dim rngDelete As Range

    Set myrange = Range("A1:A3")
    For Each mycell In myrange.Cells
        If mycell Like "*msn.com*" Or _
            mycell Like "*messaging.microsoft.com*" Or _
            Not mycell Like "*.com*" Then

            If Not rngDelete Is Nothing Then
                Set rngDelete = Union(rngDelete, mycell)
            Else
                Set rngDelete = mycell
            End If
        End If
    Next mycell

    If Not rngDelete Is Nothing Then
        rngDelete.Delete
    End If


End Sub