Delete Row syntax conundrum

[username]

I have a button where 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.

I guess I need to check the cell to see if there is a list item in it
and if so delete the row that the cell is in.

I have:

Sub IfStatement()
If Range("A20") = List Then Rows("20:20").Select & Selection.Delete
& Shift:=xlUp
End Sub

But I get compile errors - probably because I have the wrong syntax.
I'm not massively technical so can anyone help out on my syntax at all
please?

Thank you very much.


--
username
------------------------------------------------------------------------
username's Profile: http://www.msusenet.com/member.php?userid=1433
View this thread: http://www.msusenet.com/t-1870445735

[Bernie Deitrick]

If your Audience rows can be anywhere below row 20, then you could try
something like this:

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

Note that the line
myList = Array("Fred", "Sue", "Tim", "Tom")
should be modified to include those values that indicate that the row is an
"Audience" row.

HTH,
Bernie
MS Excel MVP


"username" <[email protected]> wrote in message
news:[email protected]...
>
> I have a button where 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.
>
> I guess I need to check the cell to see if there is a list item in it
> and if so delete the row that the cell is in.
>
> I have:
>
> Sub IfStatement()
> If Range("A20") = List Then Rows("20:20").Select & Selection.Delete
> & Shift:=xlUp
> End Sub
>
> But I get compile errors - probably because I have the wrong syntax.
> I'm not massively technical so can anyone help out on my syntax at all
> please?
>
> Thank you very much.
>
>
> --
> username
> ------------------------------------------------------------------------
> username's Profile: http://www.msusenet.com/member.php?userid=1433
> View this thread: http://www.msusenet.com/t-1870445735
>

[username]

This seems to work well - for which, thanks.

The only issue is that if I specify in myList array (for example) "a",
"b", "c" & "d" as the characters to search for and I put in another
character into one of cells after A21, for example "s", when I press my
'Delete Row' button it also deletes the "s" as well as any of the
specified characters. Any ideas how to make it only delete those
characters/words that I have specified in myList?

Thanks again.


--
username
------------------------------------------------------------------------
username's Profile: http://www.msusenet.com/member.php?userid=1433
View this thread: http://www.msusenet.com/t-1870445735

[Bernie Deitrick]

My bad. The line

If Not IsError(Application.Match(Range("A" & i).Value, myList)) Then

should be

If Not IsError(Application.Match(Range("A" & i).Value, myList, False)) Then

Sorry about that.

HTH,
Bernie
MS Excel MVP


"username" <[email protected]> wrote in message
news:[email protected]...
>
> This seems to work well - for which, thanks.
>
> The only issue is that if I specify in myList array (for example) "a",
> "b", "c" & "d" as the characters to search for and I put in another
> character into one of cells after A21, for example "s", when I press my
> 'Delete Row' button it also deletes the "s" as well as any of the
> specified characters. Any ideas how to make it only delete those
> characters/words that I have specified in myList?
>
> Thanks again.
>
>
> --
> username
> ------------------------------------------------------------------------
> username's Profile: http://www.msusenet.com/member.php?userid=1433
> View this thread: http://www.msusenet.com/t-1870445735
>

[username]

It seems that your code works a little too well!

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 again


--
username
------------------------------------------------------------------------
username's Profile: http://www.msusenet.com/member.php?userid=1433
View this thread: http://www.msusenet.com/t-1870445735

[Bernie Deitrick]

The line

If Not IsError(Application.Match(Range("A" & i).Value, myList)) Then

should be

If Not IsError(Application.Match(Range("A" & i).Value, myList, False)) Then

Sorry about that.

HTH,
Bernie
MS Excel MVP


"username" <[email protected]> wrote in message
news:[email protected]...
>
> It seems that your code works a little too well!
>
> 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 again
>
>
> --
> username
> ------------------------------------------------------------------------
> username's Profile: http://www.msusenet.com/member.php?userid=1433
> View this thread: http://www.msusenet.com/t-1870445735
>