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

 

[RichJW]

Many thanks to you both.

My Aswer Is This - I'm pleased you used the "Or" statement in the middle of it, so I could see how you referenced it, also thanks for the clarification on what that line of text meant.

Rick - thanks again.

Both worked beautifully, however Rick's was quicker so I've used that.

Cheers,
Rich

 

[My Aswer Is This]

Glad I could help you. Yes Rick always comes up with code that is quicker then mine. I cannot understand Ricks code but it works.

Many thanks to you both.

My Aswer Is This - I'm pleased you used the "Or" statement in the middle of it, so I could see how you referenced it, also thanks for the clarification on what that line of text meant.

Rick - thanks again.

Both worked beautifully, however Rick's was quicker so I've used that.

Cheers,
Rich

 

[Corsa88]

I have this 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

However, it runs very slowly on my sheet with about 100,000 data rows. Any advice on making it faster?

 

[MARK858]

With apologies to Mirabeau for corrupting the code previously posted, try the code below on a copy of your worksheet.

Sub delme21()
    Dim x, lr As Long, lc As Integer
    Dim a, b() As Variant, i As Long, e, k As Boolean
    Application.ScreenUpdating = False
    e = "Record Only"

    lr = ActiveSheet.Cells.Find("*", searchorder:=xlByRows, _
                                searchdirection:=xlPrevious).Row
    lc = ActiveSheet.Cells.Find("*", searchorder:=xlByColumns, _
                                searchdirection:=xlPrevious).Column

    a = Cells(1, "D").Resize(lr)
    ReDim b(1 To lr, 1 To 1)
    For i = 1 To lr
        If InStr(a(i, 1), e) > 0 Then
            b(i, 1) = 1
            k = True
        End If
    Next i

    If k = False Then Exit Sub
    Cells(1, lc + 1).Resize(lr) = b
    Cells(1, 1).Resize(lr, lc + 1).Sort Cells(1, lc + 1), 1
    Cells(1, 1).Resize(Cells(1, lc + 1).End(xlDown).Row, lc + 1).Delete 3
    Application.ScreenUpdating = True
End Sub

 

[BrianB]

Also add at the beginning :

Application.Calculation = xlCalculationManual

and at the end :=

Application.Calculation = xlCalculationAutomatic

 

[MARK858]

Also add at the beginning :

Application.Calculation = xlCalculationManual

and at the end :=

Application.Calculation = xlCalculationAutomatic

If Corsa88 is using formulas in the workbook I agree.

 

[Corsa88]

Brian and Mark,

Thanks for the reply. Just a question, i have about 20 sheets of data, and the entire excel file is 1.7 gb. Running it manually (i.e. click each sheet and run code individually) would take around 20 mins. Is it reasonable to expect that a VBA macro would yield run times lesser than that? Currently it runs very slow, up to a couple of hours in fact.

 

[MARK858]

Currently it runs very slow, up to a couple of hours in fact.

Do you have a huge amount of formulas and formatting on each sheet?
The reason I ask is I just run the code in post #74 on a sheet with columns A to J by 135168 rows having data, where 45056 rows met the criteria to delete and it ran in under 700 milliseconds on every run.

 

[Corsa88]

Do you have a huge amount of formulas and formatting on each sheet?
The reason I ask is I just run the code in post #74 on a sheet with columns A to J by 135168 rows having data, where 45056 rows met the criteria to delete and it ran in under 700 milliseconds on every run.

Mark,


I see. For the code in #74, if i change the criteria to a value greater than, say 50, do I change this:

[COLOR=#574123]e = "Record Only"[/COLOR]

to

[COLOR=#574123]e = ">50[/COLOR][COLOR=#574123]"[/COLOR]

Also, to change the column to column 'V' starting on the 2nd row, do I change:

[COLOR=#574123]a = Cells(1, "D")[/COLOR]

to

[COLOR=#574123]a = Cells(2, "V")[/COLOR]

?

 

[MARK858]

The code in post #74 was for dealing with a text string not a greater than condition as per your question in post #73 which was an autofilter on Column D for a text string with wildcards.

I am not home to write code now but for when I am are you sure what is in post #79 is what you actually want?