We help IT Professionals succeed at work.

Excel Delete Row if Cell in a PArticular Column Contains

allanch08 asked
Hello experts,

I have an excel file with about 10,000 rows of info:

A            B                  C               ---->      J
id         FisrtName   LastName                City

Does anyone know how I can delete all rows in a column (column J) if a cell in that column contains a certain word (London)? Thanks
Watch Question

Honestly the quickest and easiest way is to sort the spreadsheet by column J, filter for the value you want to delete (London), highlight the rows and delete them manually.  If there is a reason you need to get a fancier solution then can you please elaborate?
John MeggersNetwork Architect

Agreed.  Some things aren't worth automating...


Thanks, I was hoping excel had a simple command that could do that
To do this in code ...
With ThisWorkbook.Worksheets("Sheet1")
      .[j1].AutoFilter Field:=1, Criteria1:="=*London*", Operator:=xlAnd
      If Not Intersect(.UsedRange, .Range(.[j2], .Cells(.Rows.Count, 1)).SpecialCells(xlCellTypeVisible)) _
          Is Nothing Then
          Intersect(.UsedRange, .Range(.[j2], .Cells(.Rows.Count, 1)).SpecialCells(xlCellTypeVisible)).EntireRow.Delete
      End If
  End With

Open in new window

Sort will certainly work.  Filter will allow you to select out more than one value at a time, and create a custom filter, then delete those that match.  This may be faster than sort, just depends on your data/situation.
Rob HensonFinance Analyst
No need to apply the sort before applying the filter.

Rows that are hidden by a filter will not be affected.

So if you apply a filter to COlumn J and show only those containing London for example. Highlighting the visible cells and deleting them will only delete those rows. You don't have to select the whole row as the filter will assume this and give warning message to that effect.

Rob H

True, I just like it better that way.  :)


Thanks for your help!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.