fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

39 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!

    • eyeit says:

      I didn't know that it works on Mac too. I only use Windows. 
      Thans for telling me it also works on Mac! 🙂

  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

  17. merk says:

    Thanks! It works great.

  18. santosh says:

    Thanks you sir, you saved my brain & Time... Thanks Alot

  19. Bala says:

    Thanks you sir, you saved my brain & Time… Thanks Alot

  20. MKL says:

    When I select control, nothing happens. Any ideas? If I right click to delete it deletes the entire spreadsheet.

  21. Martha says:

    THANK YOU! Of all the tips/tricks I found, this one was by far the easiest, faster, and most understandable one for what I needed.

  22. nia says:

    awesome! That's so smart 😀
    Thank you! ^_^

  23. Viola says:

    Wow !! it worked perfectly for me. Thank you, you just saved me HOURS of work.

  24. Dave says:

    Thanks for sharing - 7500 line spreadsheet...reduced down to 2400 with this trick for getting rid en-masse all the lines containing things I don't care about.
    Thank you!

  25. Carl-Etienne Juneau says:

    Awesome tip. Thanks!

    PS. I really like how your blog is set up. Which plugin did you use to set up the 3 choices below comments? These 3:

    Notify me of when new comments are posted via e-mail

    Notify me of follow-up comments by email.

    Notify me of new posts by email.

  26. Abdul Haleem Khudam Sethi says:

    Plz sent me exccel Formullah's
    i m student
    m B.A, B.Ed

  27. JOHN says:

    WHAT"S THIS ALL ABOUT????????

  28. Jack says:

    I have a workbook with many different sheets. I'm looking to delete all cells in all worksheets that have a specific words (e.g. Harris). I want to be able to do this time and time again as names get crossed off. Is there a way to easily do this?

  29. Shreenath says:

    Need to bifurcate as Parent & Child data from raw data which in in single column. Here is the sample data.

    Raw Data Parent Child
    1. AAA 1. AAA a1
    a1 1. AAA a2
    a2 1. AAA a3
    a3 1. AAA a4
    a4 1. AAA a5
    a5 1. AAA a6
    a6 2. BBB b1
    2. BBB 2. BBB b2
    b1 2. BBB b3
    b2 2. BBB b4
    b3 2. BBB b5
    b4 3. CCC c1
    b5 3. CCC c2
    3. CCC 3. CCC c3
    c1 3. CCC c4
    c2 3. CCC c5
    c3 4. DDD d1
    c4 4. DDD d2
    c5 4. DDD d3
    4. DDD 4. DDD d4
    d1 4. DDD d5
    d2 5. EEE e1
    d3 5. EEE e2
    d4 5. EEE e3
    d5 5. EEE e4
    5. EEE 5. EEE e5
    e1
    e2
    e3
    e4
    e5

Leave a Reply