Page 1 of 6 123 ... LastLast
Results 1 to 10 of 52

VBA Delete Entire Row if Contains Certain Text

This is a discussion on VBA Delete Entire Row if Contains Certain Text within the Excel Questions forums, part of the Question Forums category; I've searched on here, but every code I put in gives me an error back. Data in column D, If ...

  1. #1
    Board Regular
    Join Date
    Jul 2005
    Posts
    93

    Default VBA Delete Entire Row if Contains Certain Text

    I've searched on here, but every code I put in gives me an error back. Data in column D, If any of the cells contains "Record Only" I need it to delete the entire row.
    Thanks

  2. #2
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,108

    Default Re: VBA Delete Entire Row if Contains Certain Text

    Regards
    BrianB (using XL2003 & 2010)
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

  3. #3
    Board Regular Mark F's Avatar
    Join Date
    Jun 2002
    Location
    Biggleswade, Beds , England
    Posts
    452

    Default Re: VBA Delete Entire Row if Contains Certain Text

    Hi

    Try the code below

    Hope it works for you

    Mark
    Code:
     
    Sub DeleteRowWithContents()
    '========================================================================
    ' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORDs "Record Only" IN COLUMN D
    '========================================================================
        Last = Cells(Rows.Count, "D").End(xlUp).Row
        For i = Last To 1 Step -1
            If (Cells(i, "D").Value) = "Record Only" Then
        'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
                Cells(i, "A").EntireRow.Delete
            End If
        Next i
    End Sub

  4. #4
    New Member
    Join Date
    Feb 2009
    Posts
    3

    Default Re: VBA Delete Entire Row if Contains Certain Text

    Hi,

    That was really helpful. Is there a way to do the same thing, but instead of having to match the words "Record Only" the cell could contain those words, but other words as well?

    Thanks,
    Daniel



    Quote Originally Posted by Mark F View Post
    Hi

    Try the code below

    Hope it works for you

    Mark
    Code:
     
    Sub DeleteRowWithContents()
    '========================================================================
    ' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORDs "Record Only" IN COLUMN D
    '========================================================================
        Last = Cells(Rows.Count, "D").End(xlUp).Row
        For i = Last To 1 Step -1
            If (Cells(i, "D").Value) = "Record Only" Then
        'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
                Cells(i, "A").EntireRow.Delete
            End If
        Next i
    End Sub

  5. #5

    Join Date
    Oct 2006
    Posts
    2,541

    Default Re: VBA Delete Entire Row if Contains Certain Text

    Why loop ?
    Try AutoFilter ...
    Code:
    Sub test()
    With ActiveSheet
        .AutoFilterMode = False
        With Range("d1", Range("d" & Rows.Count).End(xlUp))
            .AutoFilter 1, "*Record Only*"
            On Error Resume Next
            .Offset(1).SpecialCells(12).EntireRow.Delete
        End With
        .AutoFilterMode = False
    End With
    End Sub

  6. #6
    New Member
    Join Date
    Feb 2009
    Posts
    3

    Default Re: VBA Delete Entire Row if Contains Certain Text

    Wow, much better...thanks!
    Is there a way to do the same thing, but instead of a specific text, look for a number greater than 50- until now I am using:


    Last = Cells(Rows.Count, "U").End(xlUp).Row
    For i = Last To 1 Step -1
    If (Cells(i, "U").Value) > 50 Then

    Cells(i, "A").EntireRow.Delete
    End If
    Next i

  7. #7

    Join Date
    Oct 2006
    Posts
    2,541

    Default Re: VBA Delete Entire Row if Contains Certain Text

    Jus change

    "*Record Only*"

    to

    ">50"

  8. #8
    New Member
    Join Date
    Feb 2009
    Posts
    3

    Default Re: VBA Delete Entire Row if Contains Certain Text

    Wow, thanks so much- you're the man!

  9. #9
    New Member
    Join Date
    Apr 2005
    Posts
    18

    Default Re: VBA Delete Entire Row if Contains Certain Text

    Very helpfull thread. Is there a way to "clear" entire row if column "A" CONTAINS ANY TEXT?
    Or if column "A" AND "B" contains any text?

  10. #10
    New Member
    Join Date
    Jun 2009
    Posts
    3

    Default Re: VBA Delete Entire Row if Contains Certain Text

    I've tried the code above and it works perfectly for cells where I have a constant I'm searching on. I've also been trying to delete rows where the date is greater than a named range on one of my spreadsheets. Here is the code I have.

    [Code]
    Sub DateExtract()
    Dim DateTime As Range, Cell As Object
    Dim FutureDate As Range

    Set DateTime = Range("C57:C1000") 'Range containing expected funding date
    Set FutureDate = Sheets("Report Roster").Range("H2") 'Range containing current month's date
    With ActiveSheet
    .AutoFilterMode = False
    With Range("A56", Range("M" & DateTime.Rows.Count + 56).End(xlUp))
    .AutoFilter 3, ">FutureDate"
    On Error Resume Next
    .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
    End With
    [End Code]

    My issue I'm having is nothing is being deleted because the criteria in the autofilter is inserting the name >Futerdate. Is there a way to make that variable based on the date I have in my spreadsheet.

    More specifically, the date is 3 months from the prior month end date I'm running the report for. So for my April report the date is giving me 7/31/2009. But next month it will be 8/31/2009.

Page 1 of 6 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com