+ Reply to Thread
Results 1 to 4 of 4

Delete rows that do no contain a specific word

Hybrid View

  1. #1
    Registered User
    Join Date
    04-02-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Delete rows that do no contain a specific word

    Hi,

    I'm trying to write a macro that deletes a row if column B does not contain the word "DOGS"

    There is a different number of rows every day.

    Any help would be greatly appreciated,

    Thanks!

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,565

    Re: Delete rows that do no contain a specific word

    Hi Tesseh (and fellow Sydneyite ),

    Try this (initially on a copy of your data as results cannot be undone if they're not as expected):

    Option Explicit
    Sub Macro2()
    
        'Written by Trebor76
        'Visit my website wwww.excelguru.net.au
        
        'Delete all rows where the text 'DOGS' is not in Col. B from the row number passed to the constant variable 'lngStartRow'.
        
        Const lngStartRow As Long = 2 'Starting row number for your data. Change to suit.
        
        Dim lngMyCol As Long, _
            lngMyRow As Long
        Dim xlnCalcMethod As XlCalculation
                
        With Application
            xlnCalcMethod = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With
    
        lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
        lngMyRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
        With Columns(lngMyCol)
            With Range(Cells(lngStartRow, lngMyCol), Cells(lngMyRow, lngMyCol))
                .Formula = "=IFERROR(FIND(""DOGS"",B" & lngStartRow & "),NA())"
                ActiveSheet.Calculate
                .Value = .Value
            End With
            On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
                .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
            On Error GoTo 0 'Turn error reporting back on
            .Delete
        End With
        
        With Application
            .Calculation = xlnCalcMethod
            .ScreenUpdating = True
        End With
    
        MsgBox "All applicable rows have now been deleted.", vbInformation, "Excel Guru"
    
    End Sub
    Note that the code is looking for the text 'DOGS' in that case. If your not worried about case sensitivity (i.e. 'Dogs', 'dogs', etc...), change this line of code...

    .Formula = "=IFERROR(FIND(""DOGS"",B" & lngStartRow & "),NA())"
    ...to this:

    .Formula = "=IFERROR(SEARCH(""DOGS"",B" & lngStartRow & "),NA())"
    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Registered User
    Join Date
    04-02-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Delete rows that do no contain a specific word

    Robert, did I ever thank you for this? I use this macro everyday and it works perfectly. Thanks so much!!!!

  4. #4
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,565

    Re: Delete rows that do no contain a specific word

    No, I didn't think you ever did but that's fine - you have now

    If you could mark the thread as closed it would be appreciated.

    Thanks,

    Robert

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] delete rows when column has specific word(s)
    By chrisblackman in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 09-22-2013, 10:21 PM
  2. [SOLVED] Macro to delete rows that DO NOT contain specific word
    By enfieldsteve in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-19-2013, 10:59 AM
  3. [SOLVED] Need a macro to delete rows with specific word
    By renjithvakkayil in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-07-2013, 12:54 PM
  4. Delete all rows that dont contain a specific word.
    By BPSJACK in forum Excel General
    Replies: 4
    Last Post: 03-15-2012, 05:05 AM
  5. Macro to look for a specific word and delete rows
    By excelaspire0219 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-27-2009, 06:35 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1