Delete Row syntax conundrum
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 |
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" wrote in message ... 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 |
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 |
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 |
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" wrote in message ... 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 |
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" wrote in message ... 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 |
All times are GMT +1. The time now is 04:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com