VBA Delete Entire Row if Contains Certain Text

[bigmacneb]

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

 

[BrianB]

Have a look at my code here:-

http://www.mrexcel.com/forum/showthread.php?p=761632

 

[Mark F]

Hi

Try the code below

Hope it works for you

Mark

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

 

[DanielHochman]

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

Hi

Try the code below

Hope it works for you

Mark

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

 

[Legacy 68668]

Why loop ?
Try AutoFilter ...

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

 

[DanielHochman]

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

 

[Legacy 68668]

Jus change

"*Record Only*"

to

">50"

 

[DanielHochman]

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

 

[Rip-N-Burn]

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?

 

[RyanR]

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.

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.