Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Delete Rows That Contain Specific Words

  1. #1
    Join Date
    11th November 2006
    Posts
    6

    Delete Rows That Contain Specific Words

    I am brand new to VBA and really only need to know how to create one macro: I have a spreadsheet with 8 columns with various text headings. Each column has different data (some show dates, some show only figures, some show text). Specifically, column E (entitled "Sub Item Reference") has various text entries (e.g. "Newsletter" or "Booklet"). I am hoping to find a simple macro that will search column E and delete any rows where that column contains the text "Cover" or "Label". I'm sure it's the simplest thing going but I can't seem to work it out. Many thanks.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd November 2005
    Posts
    894

    Re: If Column Contains Specific Words Delete That Row

    give this a try
    Code:
    Sub DeleteRows()
        Dim rFilter As Range, rFormula As Range, LastRow As Long
        
        Application.ScreenUpdating = False
        
        With Sheet1
            .Range("F1").Value = "Helper"
            LastRow = .Range("A65536").End(xlUp).Row
            Set rFormula = .Range("F2:F" & LastRow)
            Set rFilter = .Range("A1:F1")
        End With
        
        rFormula = "=OR(E2=""label"",E2=""cover"")"
        On Error Resume Next 'In case there are no matches
        With rFilter
            .AutoFilter
            .AutoFilter field:=6, Criteria1:="True"
            .Offset(1, 1).Resize(.CurrentRegion.Rows.Count - 3, 1).SpecialCells(xlCellTypeVisible). _
            EntireRow.Delete
            .AutoFilter
        End With
        On Error GoTo 0
        Application.ScreenUpdating = True
    End Sub
    Last edited by JMAN; November 11th, 2006 at 00:53.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    11th November 2006
    Posts
    6

    Re: If Column Contains Specific Words Delete That Row

    Wow, you are incredible!! It works beautifully. Just one more question that didn't occur to me before, if it's not being too cheeky - if the search word is only part of the whole text (e.g. the text says "CDA5486 Label" rather than just "Label" and the CDA code numbers are always different) is there a way to pick out those instances and delete the rows the same as it does when the text just says "Label"? Thanks again for your assistance.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    22nd November 2005
    Posts
    894

    Re: If Column Contains Specific Words Delete That Row

    Will the word "label" or "cover" always be in the same position? What I mean is, will it always be the on right side like that?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    11th November 2006
    Posts
    6

    Re: If Column Contains Specific Words Delete That Row

    Yes, that's right - always on the right with (sometimes) an identifier number appended to the left.
    Last edited by sfjnet; November 11th, 2006 at 01:17.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    22nd November 2005
    Posts
    894

    Re: If Column Contains Specific Words Delete That Row

    Try this. NOTE - There is probably a better formula for this so maybe someone will chime in with a more effecient solution. The problem with this formula is that if you have any spaces after the word label or cover then it will return false b/c it will count that space as a value. FYI

    Code:
    Sub DeleteRows()
        Dim rFilter As Range, rFormula As Range, LastRow As Long
        
        Application.ScreenUpdating = False
        
        With Sheet1
            .Range("F1").Value = "Helper"
            LastRow = .Range("A65536").End(xlUp).Row
            Set rFormula = .Range("F2:F" & LastRow)
            Set rFilter = .Range("A1:F1")
        End With
        
        rFormula = "=OR(RIGHT(E3,5)=""label"",RIGHT(E3,5)=""cover"")"
        On Error Resume Next 'In case there are no cells to set to pending
        With rFilter
            .AutoFilter
            .AutoFilter field:=6, Criteria1:="True"
            .Offset(1, 1).Resize(.CurrentRegion.Rows.Count - 3, 1).SpecialCells(xlCellTypeVisible). _
            EntireRow.Delete
            .AutoFilter
        End With
        On Error GoTo 0
        Application.ScreenUpdating = True
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    11th November 2006
    Posts
    6

    Re: If Column Contains Specific Words Delete That Row

    A ha! That is why it works perfectly except for two entries that won't be deleted - there is a space after the word! Anyway, thanks again - you have really helped me out.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,786

  9. #9
    Join Date
    29th December 2015
    Posts
    7

    Re: If Column Contains Specific Words Delete That Row

    Hi,
    My request is very similar to the original post i just dont know how to change the code in post #2, as it seems like it would do the job!.
    What changes would i need to make to the code in post#2 here so that in column A....

    I can delete rows that contain the words "blank" "empty" "Apple pies " and completely empty cells ""

    I do not have column headings/titles so the search is for all column A.
    I am currently doing this manually by conditional formatting to highlight the cells and then deleting the rows....long tedious work.

    In essence, the vba would search column A looking for text "X" "Y" "Z" and empty cell "" and remove the rows that contain them.

    I hope this made sense. Also I hope it was ok to post this here instead of a new thread...my apologies if I was wrong.
    Many thanks in advance
    Neil

    PS I found useful code on this thread using the 'input box' method from Reafidy post #4, but i have to enter search strings separately
    http://www.ozgrid.com/forum/showthread.php?t=66221
    Last edited by NeilE; December 29th, 2015 at 05:55.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    14th November 2013
    Location
    Cheshire, UK
    Posts
    2,185

    Re: Delete Rows That Contain Specific Words

    Hi NeilE, welcome to Ozgrid.

    As you have hinted in your post, you need to post a new thread and provide a link back to this one if you feel it helps to clarify your problem. The reason for this is not only to prevent old threads being re-activated - but also because asking your own question on another member's thread is prohibited.

    Please start a new thread for your question, if you need further assistance please do not hesitate to PM me.

    Kind Regards,

    S O

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 6
    Last Post: August 5th, 2008, 06:07
  2. Delete Rows Based On Words From Another Sheet
    By pilch in forum EXCEL HELP
    Replies: 5
    Last Post: February 5th, 2008, 18:13
  3. Replies: 5
    Last Post: December 11th, 2007, 19:59
  4. Delete Specific Rows
    By h in forum EXCEL HELP
    Replies: 7
    Last Post: September 14th, 2007, 01:20
  5. Delete specific rows
    By Timbo in forum EXCEL HELP
    Replies: 2
    Last Post: February 11th, 2005, 21:01

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno