SOLVED

Deleting rows that contain specific content

%3CLINGO-SUB%20id%3D%22lingo-sub-2084473%22%20slang%3D%22en-US%22%3EDeleting%20rows%20that%20contain%20specific%20content%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2084473%22%20slang%3D%22en-US%22%3E%3CP%3ECopying%20records%20from%20a%20source%20into%20excel%2C%20each%20record%20copies%20over%20as%20either%202%20or%203%20lines.%26nbsp%3B%20Nearly%20every%20record%20contains%20a%20unique%20line%20with%20the%20word%20%22search%22%20in%20it.%26nbsp%3B%20I%20don't%20need%20those%20lines%20in%20my%20spreadsheet%2C%20but%20don't%20know%20how%20to%20write%20a%20macro%20that%20will%20delete%20only%20rows%20that%20contain%20that%20specific%20word.%26nbsp%3B%20I'm%20sure%20its%20simple%2C%20but%20my%20programming%20knowledge%20is%20minimal.%26nbsp%3B%20Any%20help%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2084473%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2084557%22%20slang%3D%22en-US%22%3ERe%3A%20Deleting%20rows%20that%20contain%20specific%20content%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2084557%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F938199%22%20target%3D%22_blank%22%3E%40RichardA2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20do%20this%20without%20programming%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20in%20the%20Sort%20%26amp%3B%20Filter%20group%2C%20turn%20on%20Filter.%3C%2FLI%3E%0A%3CLI%3EFrom%20the%20filter%20dropdown%20in%20the%20relevant%20column%2C%20select%20Text%20Filters%20%26gt%3B%20Contains...%3C%2FLI%3E%0A%3CLI%3EEnter%20%3CSTRONG%3ESearch%3C%2FSTRONG%3E%20in%20the%20box%2C%20then%20click%20OK.%3C%2FLI%3E%0A%3CLI%3EYou%20should%20now%20see%20only%20the%20rows%20containing%20%3CSTRONG%3ESearch%3C%2FSTRONG%3E.%3C%2FLI%3E%0A%3CLI%3EDelete%20those%20rows.%3C%2FLI%3E%0A%3CLI%3ETurn%20off%20the%20filter.%3C%2FLI%3E%0A%3C%2FUL%3E%3C%2FLINGO-BODY%3E
New Contributor

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!

5 Replies
best response confirmed by RichardA2021 (New Contributor)
Solution

@RichardA2021 

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.

@Hans Vogelaar 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 ]

 

@RookBytes 

Using the AutoFilter method, you only need to select all rows except the top one after filtering and delete them in one go. Easy-peasy.

Ah, ok. I thought that would delete the hidden rows too. Thanks, Hans