Announcement

Collapse
No announcement yet.

Delete Rows That Contain Specific Words

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    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, 00:53.

    Comment


    • #3
      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.

      Comment


      • #4
        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?

        Comment


        • #5
          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, 01:17.

          Comment


          • #6
            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

            Comment


            • #7
              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.

              Comment


              • #8
                Re: If Column Contains Specific Words Delete That Row

                See Also delete rows based on criteria

                Comment


                • #9
                  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, 05:55.

                  Comment


                  • #10
                    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

                    Comment

                    Working...
                    X