Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to delete blank rows | Excel Discussion (Misc queries) | |||
Macro to delete data in 'green' cells only | Excel Worksheet Functions | |||
How to delete macros | Excel Discussion (Misc queries) | |||
delete row contains specific word in an macro | Excel Discussion (Misc queries) | |||
Saving to a network share without delete permssions | Excel Discussion (Misc queries) |