Forum Discussion
Solved
Deleting rows that contain specific content
Copying records from a source into excel, each record copies over as either 2 or 3 lines. Nearly every record contains a unique line with the word "search" in it. I don't need those lines in my spreadsheet, but don't know how to write a macro that will delete only rows that contain that specific word. I'm sure its simple, but my programming knowledge is minimal. Any help would be greatly appreciated!
lucahla Sorry to interrupt, but have you had a chance to look at the file I sent earlier? It contains a list where you can enter the snippets you indexed. The formula (in B2 and copied down)
=IFERROR(MATCH(TRUE,(ISNUMBER(SEARCH($D$2:$D$7,A2,1))),0),"")will give you the position in the list when a snippet is found in the text in A. Just amend to cell references and list range to fit your real worksheet.
Now, you added another dimension to your question by stating that the snippets are always in the last two positions of the text. In that case you could expand the reference to A2 in the above formula to become RIGHT(A2,2) . This will avoid incorrect indexing in case your text would be like "ABC3456-78_9 DE" where it would pickup the BC as the snippet, assuming your snippet-list is alphabetically sorted. Can even think of some more variations to avoid this. Not sure, though, that this could ever occur. So, perhaps it's totally irrelevant.
10 Replies
- When I do this, and try to delete all the rows, I can't. Column A is a merged cell and I get an error stating I can't delete this. Any ideas on a workaround?
Avoid using merged cells...
You can do this without programming:
- On the Home tab of the ribbon, in the Sort & Filter group, turn on Filter.
- From the filter dropdown in the relevant column, select Text Filters > Contains...
- Enter Search in the box, then click OK.
- You should now see only the rows containing Search.
- Delete those rows.
- Turn off the filter.
HansVogelaar Thank you... that was simple!
RichardA2021 @Hans Vogelaar Thank you both for this idea. I have a follow-on question too, if you don't mind, Hans:
What if the number of Rows I needed to get rid of were so many that it becomes impractical to delete them individually. Is there a way to say to Excel: "Delete all rows where the column WO_Type = CLOSED." And then for example, 1,500 rows are deleted instantaneously.
Thank you both for your time and consideration.
Best regards,
Rob Allsbrook [ @RookBytes ]
