Delete row if cell contains specific data

[wilkisa]

I need a quick macro that will search column D for the words "balance forward" then delete the entire row. The macro needs to loop through many rows and it contain more than one "balance forward" reference.

Can someone help? I'm just not good at macros yet.

Thanks,
Shirlene

 

[texasalynn]

try this

Sub drow()
Dim LR As Long
LR = Cells.SpecialCells(xlCellTypeLastCell).Row
    For i = LR To 2 Step -1
        If Cells(i, 4) <> "Balance Forward" Then
        Rows(i).EntireRow.Delete
        End If
    Next
 End Sub

 

[wilkisa]

Thanks, Texasalynn, but that deleted every single row from my table and left only the headings. Egad, what happened?

 

[Norie]

Shirlene

Could you not just use a filter to hide the rows?

Do they really need deleted?

 

[wilkisa]

Yes, my customer really wants them deleted. I already suggested the filter and that is not what he wants. I'm trying to step through the macro right now to see where it is failing but not having a lot of luck.

 

[wilkisa]

This macro deletes the grand total row then every single row from the bottom up, regardless of the text in Column D. It stops when it gets to Row 1 where the headers are.

 

[Norie]

Well perhaps the problem is that the code is looking for data not equal to "Balance Forward"?

 

[Legacy 51064]

The Autofilter method works faster than the loop when you have large amounts of data.

'Filters the and deletes the data
Sheets("Database").Activate

    Rows("1:1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=4, Criteria1:="Balance Forward"
        
    ActiveSheet.UsedRange.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete Shift:=xlUp

ActiveSheet.AutoFilterMode = False

 

[wilkisa]

Yes, I thought that as well and changed to an '=' sign. I reran the macro and absolutely nothing at all happened. I kept thinking "I'll wait until it is done" but it never really started. Again, I tried stepping through but nothing at all.....

 

[wilkisa]

98illini, thanks so much. I didn't think about using the autofilter and then deleting the rows. I guess it was just too simple for my simple brain! It worked like a charm.

Thanks to Norie and Texasalynn, too for your help.

Shirlene