Macro to Remove Specific Rows

[linaeum66]

I'm interested in a macro that removes an entire row if the sentence in cell "O' doesn't end in a period. Also if the macro could ignore the first row which is headings.
Thanks for your help, greatly appreciated.

 

[alansidman]

Option Explicit


Sub NoPeriod()
    Dim i As Long, lr As Long
    lr = Cells(Rows.Count, "O").End(xlUp).Row
    For i = lr To 2 Step -1
        If InStr(1, Range("O" & i), ".") = 0 Then
            Range("O" & i).EntireRow.Delete
        End If
    Next i


End Sub

 

[linaeum66]

Thank you for the quick response. This macro worked perfectly on a small file (100 rows) but when I tried to use it on a large file of 7000 rows it gave me the following error "type mismatch". Is there any way to work around that?
Thanks again for your help, greatly appreciated.

Option Explicit


Sub NoPeriod()
    Dim i As Long, lr As Long
    lr = Cells(Rows.Count, "O").End(xlUp).Row
    For i = lr To 2 Step -1
        If InStr(1, Range("O" & i), ".") = 0 Then
            Range("O" & i).EntireRow.Delete
        End If
    Next i


End Sub

 

[linaeum66]

Scratch that, It is working now! thanks

Thank you for the quick response. This macro worked perfectly on a small file (100 rows) but when I tried to use it on a large file of 7000 rows it gave me the following error "type mismatch". Is there any way to work around that?
Thanks again for your help, greatly appreciated.

 

[alansidman]

you are welcome

 

[Rick Rothstein]

Option Explicit


Sub NoPeriod()
    Dim i As Long, lr As Long
    lr = Cells(Rows.Count, "O").End(xlUp).Row
    For i = lr To 2 Step -1
        [B][COLOR="#FF0000"]If InStr(1, Range("O" & i), ".") = 0 Then[/COLOR][/B]
            Range("O" & i).EntireRow.Delete
        End If
    Next i


End Sub

One possible problem with the red colored code line above... if the sentence has an abbreviation in it, but doesn't have a period at the end, for example...

Dr. Smith went home

this code line will not let that row be deleted. Change the red highlighted line of code to this and it should then work in all circumstances..

If Cells(i, "O").Value Like "*[!.]" Then