Delete Row syntax conundrum

I have a button that Users can click to create new rows to specify
'Audiences' for a communication. When they click the button a new Audience
row with a cell with a drop down in appears each time. However when I want to
'Reset' the worksheet I only want there to be one Audience row remaining.

The code I currently have is as follows:

Sub IfStatement2()
Dim myList As Variant
Dim i As Long
myList = Array("Fred", "Sue", "Tim", "Tom")
For i = Range("A65536").End(xlUp).Row To 21 Step -1
If Not IsError(Application.Match(Range("A" & i).Value, myList)) Then
Range("A" & i).EntireRow.Delete
End If
Next i
End Sub

I have specified the items in the myList array accurately, but the 'match'
test seems to be a little loose in its application. Any text in the column
beneath my Audiences (even with no relation to the text specified in the
myList array) also seems to bring up a match and thus its row gets deleted.

Is there a way to force an 'exact match'?

Many thanks all.

I think you just need an exact match

Sub IfStatement2()
Dim myList As Variant
Dim i As Long
myList = Array("Fred", "Sue", "Tim", "Tom")
For i = Cells(Rows.Count, "A").End(xlUp).Row To 21 Step -1
If Not IsError(Application.Match(Range("A" & i).Value, myList, 0))
Then
Rows(i).EntireRow.Delete
End If
Next i
End Sub


--
HTH

Bob Phillips

"JFH" <[email protected]> wrote in message
news:[email protected]...
> I have a button that Users can click to create new rows to specify
> 'Audiences' for a communication. When they click the button a new Audience
> row with a cell with a drop down in appears each time. However when I want
to
> 'Reset' the worksheet I only want there to be one Audience row remaining.
>
> The code I currently have is as follows:
>
> Sub IfStatement2()
> Dim myList As Variant
> Dim i As Long
> myList = Array("Fred", "Sue", "Tim", "Tom")
> For i = Range("A65536").End(xlUp).Row To 21 Step -1
> If Not IsError(Application.Match(Range("A" & i).Value, myList)) Then
> Range("A" & i).EntireRow.Delete
> End If
> Next i
> End Sub
>
> I have specified the items in the myList array accurately, but the 'match'
> test seems to be a little loose in its application. Any text in the column
> beneath my Audiences (even with no relation to the text specified in the
> myList array) also seems to bring up a match and thus its row gets
deleted.
>
> Is there a way to force an 'exact match'?
>
> Many thanks all.
>

Thanks a lot Bob - this works a treat!