Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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" wrote in message ... 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. |
#3
|
|||
|
|||
Thanks a lot Bob - this works a treat!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Row syntax conundrum | Excel Discussion (Misc queries) | |||
How to delete blank rows | Excel Discussion (Misc queries) | |||
Cannot delete a macro | Excel Discussion (Misc queries) | |||
How to delete macros | Excel Discussion (Misc queries) | |||
delete row contains specific word in an macro | Excel Discussion (Misc queries) |