fbpx

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.

    You want to learn

    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!

    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