We help IT Professionals succeed at work.

Excel Delete Row if Cell in a PArticular Column Contains

allanch08
allanch08 asked
on
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
Comment
Watch Question

Commented:
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

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

Author

Commented:
Thanks, I was hoping excel had a simple command that could do that
Commented:
To do this in code ...
With ThisWorkbook.Worksheets("Sheet1")
      .[j1].AutoFilter
      .[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
      .[a1].AutoFilter
  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
CERTIFIED EXPERT
Commented:
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.

Thanks
Rob H

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

Author

Commented:
Thanks for your help!

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