VBA to delete entire row if any cell in column A contains both of these strings but not that

[yits05]

Hello,

I am a bit stumped trying to figure this one out and would appreciate any assistance. I am looking for a VBA script that will look through column A for any cell containing the strings year OR years but not "("" (their position within each cell will differ), and deleting that row if found. For example, in the below the first row/cell would be deleted. Many thanks.

13 Years

2012-2014 (2 years)

2011-2012 (1 year)

February 2008 - March 2009 (1 year 1 month)

 

[jsb1921]

Sub Macro1()
    Dim lastrow1 As Integer
    lastrow1 = Cells(Rows.Count, "A").End(xlUp).Row
    Dim int1 As Integer
    Dim pos1 As Integer, pos2 As Integer
    Dim str1 As String
    For int1 = lastrow1 To 1 Step -1
        str1 = UCase(Trim(LTrim(Cells(int1, "A").Value)))
        pos1 = InStr(1, str1, "YEAR", vbTextCompare)
        pos2 = InStr(1, str1, "(", vbTextCompare)
        If pos1 > 0 And pos2 <= 0 Then
            Cells(int1, "A").EntireRow.Delete
        End If
    Next int1
End Sub

If it works for you, pl mark it as solution, for the benefit of future readers of the thread.

 

[Michael M]

MAybe this

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 1 Step -1
    If InStr(Range("A" & r).Value, "(") = 0 Then Rows(r).Delete
Next r
End Sub