How to remove all cells containing John (or anything else) [Quick tip]

Posted on March 15th, 2013 in Excel Howtos - 25 comments

Here is an interesting question someone asked me recently,

If I have to delete all rows with “John” in it. Do you know how to do it? 

Well, it looks like they really hate John. But it is none of my business.

So lets go ahead and understand a dead-simple way to get rid of all cells with John or whoever else you fancy.

  1. Select all your data
  2. Press CTRL+F and search for “John”
  3. Click on “Find all” button
  4. Now, select the first result.
  5. Scroll down, holding SHIFT key, select the last result. This will select all search results.
  6. Close the Find box.
  7. Press CTRL - (to delete cells)
  8. Select “Entire row”
  9. Click ok
  10. Now poor John is history!

See this demo to understand:

How to delete all cells with a specific value in Excel?

More tutorials on removing stuff…

If you often find yourself with more data than you need & spend a lot of time deleting Johns from your data, then you will find below tutorials very useful.

 

PS: As I was traveling in last 2 weeks, I could not write often. Starting Monday, you will see your favorite awesome Excel tips regularly.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

25 Responses to “How to remove all cells containing John (or anything else) [Quick tip]”

  1. eyeitblog says:

    There’s a quicker way for this. Use the Autofilter, filter for John. Select all Rows, then press [ALT]+[;], which will only select those that are viewable. Now delete the rows with right mouse click (click on row number).

  2. PPH says:

    If you need it to reoccur programmatically, then assuming the data is in table format you can use something like this:
    Sub delete_john()
    Dim tbl As ListObject, fld As Range, c As Range, nm As String, i As Long, rws As Long
    Set tbl = ActiveSheet.ListObjects(1)
    Set fld = tbl.ListColumns(1).DataBodyRange
    rws = fld.Rows.Count
    nm = “John”
    For i = 1 To rws
        Set c = fld.Item(i)
        If c.Value = nm Then
           c.EntireRow.Delete
           i = i – 1
        End If
    Next i
    End Sub
     

    • eyeit says:

      Nice code.
      I’ve got one suggestion: It’s pretty probably that you don’t only have to selete poor John, but perhaps to delete poor Joanne for a change (could be couple! ;)). So I suggest you’ll give the happy user the possiblity to change the poor girl’s/man’s name, so why not use a cell in a sheet in which the user could change the name?
      Anyways, I still will use the Autofilter solution beceause it’s more flexible (other rows perhaps have the children’s names of John and Joanna…)

      • PPH says:

        Sure, so we’ll just set the string variable to a range.
        Replace nm = “John” with
        nm = Worksheet(“Yoursheetnamehere”).Range(“Yourcellholdingnamevaluehere”) and whoever you want to delete would be in the cell. Actually, you know what I’d do… I’d set a dynamic range to have a dropdown list of all names in column A. That dropdown selection would be the value to delete.  Then I’d assign the macro to a button.  So then deleting names would be a matter of dropdown selection, button click.  Much faster than anything else I think.
         
         
         
         

  3. Nice tip. Thank you for sharing.

  4. Mark Engelhardt says:

    Don’t forget about us Mac users – no “find all” on Excel 2011 for mac.  Autofilter worked great!

  5. zurman says:

    Chandoo: Your tip is damn easy.

  6. Ryan says:

    I would just do a replace:
    Replace “John” with “”

  7. eyeit says:

    There is a quite quicker trick for that. 
     
    Use autofilters and filter for John. Then select all rows. Enter [CTRL]+[;] which makes sure that you’ll only select rows that you can see and exclude those that are hidden by the filter.
    Delete the rows by clicking on a row numer with the right mouse button. Choose delete.
     

  8. Ankit says:

    This can also be done programmatically as:
    Sub DeleteJhon()
    For Each cl In ActiveSheet.UsedRange
    If cl.Value = “John” Then
    cl.EntireRow.Delete
    End If
    Next cl
    End Sub

    • PPH says:

      This works but it must be run multiple times because it is not accounting for the deleted rows as it makes its way through the range.

  9. Luke M says:

    Two quick notes;
    1. Instead of scrolling down in the Results field (too much work!) once you select a cell in the results field, jus hit Ctrl+a to select all of them.
    2. Several comments about Autofilter…while this would work quickly if all your data is in one column, this tip is far superior if the value you’re looking for is in multiple columns.

    • eyeit says:

      True – I didn’t realize that yet: You can delete “John” across multiple columns. Thanks for pointing that out :)
      However, the advantage of the Autofilter is that you can filter the data with more than just one column, so if you have multiple cumulative conditions, then the Autofilter is way to go. That is why I usually have to use this solution, because I usually have to choose two criterias in two different columns. 
       

  10. Tamil says:

    We can do it in another way.
    Activate Auto filter and select the name “John” Or use custom then type name , select contains (if you want partial match) or equal,  then delete entire row by simply press Ctrl and  minus. If you want partial match using “Ctrl + F” method, use “John*” in find field.

  11. ravi datt says:

    WOW!IT was so easy!

  12. Xl lent way to remove something
     

  13. M.A. Waseem says:

    It is Nice tip BUTTTTTT
    when I removed John from the List, using the given method, even Johnson which was there in the List also gone..
    A bit of caution is needed.
    M. A. Waseem

  14. Gaurav Patel says:

    Please try below in VBA
     
    Sub delrowacell ()
    Dim str As Variant
        str = ActiveCell.Value
        ‘On Error Resume Next
        Range(“A:A”).Find(What:=str, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).EntireRow.Delete
    End sub

  15. zx8754 says:

    Great tip, although I cannot think of any example case where this would be needed? I can understand to remove rows with certain value in a certain column, but in any column, why?

  16. Manoj Gupta says:

    Dear Chandoo
    Great tip, but how to use it on mac excel 2011.
    Is there a way of inserting a blank line after a certain condition.
    Regards
    Manoj

Leave a Reply