Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Delete Rows That Contain Specific Words

  1. #1
    Join Date
    10th 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
    VB:
    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 10th, 2006 at 23:53.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    10th 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
    10th 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 00: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

    VB:
    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
    10th 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,713

    Re: If Column Contains Specific Words Delete That Row


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, 05:07
  2. Delete Rows Based On Words From Another Sheet
    By pilch in forum EXCEL HELP
    Replies: 5
    Last Post: February 5th, 2008, 17:13
  3. Replies: 5
    Last Post: December 11th, 2007, 18:59
  4. Delete Specific Rows
    By h in forum EXCEL HELP
    Replies: 7
    Last Post: September 14th, 2007, 00:20
  5. Delete specific rows
    By Timbo in forum EXCEL HELP
    Replies: 2
    Last Post: February 11th, 2005, 20: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