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" 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. |
Thanks a lot Bob - this works a treat!
|
All times are GMT +1. The time now is 09:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com