VBA Delete Entire Row if Contains Certain Text

bigmacneb

Board Regular
Joined
Jul 12, 2005
Messages
93
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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
 
Upvote 0
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:)
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top