Macro to delete rows where text string appears in cell

lazzareo

New Member
Joined
Oct 13, 2009
Messages
28
I have an Excel document with 1m rows and I want delete all rows where the string ' *UAT' or ' *DEV' appears in column K.

I would normally use basic excel functions such as Findor Filters but due to the size of the sheet it either times out or freezes whenever I try.

I was hoping there would be a way round this by using a VBA Macro and therefore hoping you guys can provide some most welcome guidance.

Thanks
 
Last edited:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
A million rows is right at the limit of what Excel can handle. You may want to consider something that is built to handle that many records, like some kind of Database program.

If you want to try a macro, consider this one:

Code:
Sub DeleteRows()

    On Error Resume Next
    With Columns("K")
        .Replace "~*UAT", "#N/A", xlWhole, , False, , False, False
        .Replace "~*DEV", "#N/A", xlWhole, , False, , False, False
        .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    End With
    
End Sub
If you intended the "*" as a wildcard, and not the actual asterisk character, remove the ~ from in front of the *.
 
Upvote 0
Here is some code that uses loops. I do not know if it will work on 1 million records though. It might, but it might be a little slow (loops are pretty inefficient):
Code:
Sub MyDeleteRows()

    Dim lastRow As Long
    Dim myRow As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column K
    If Cells(Rows.Count, "K") = "" Then
        lastRow = Cells(Rows.Count, "K").End(xlUp).Row
    Else
        lastRow = Cells(Rows.Count, "K").Row
    End If
    
'   Loop through all rows, checking each one
    For myRow = lastRow To 1 Step -1
        If InStr(Cells(myRow, "K"), "UAT") + InStr(Cells(myRow, "K"), "DEV") > 0 Then
            Rows(myRow).Delete
        End If
    Next myRow
    
    Application.ScreenUpdating = True
            
End Sub
Eric,
I like the approach you are taking, but for some reason, it doesn't work for me when I tested it.
:confused:
 
Upvote 0
Eric,
I like the approach you are taking, but for some reason, it doesn't work for me when I tested it.
:confused:

Interesting. Does it just not delete anything? Does it put the #N/A in the column?

I tested this on Excel 2013 earlier, and just now on Excel 2016, both worked fine. Only on a few dozen rows though, maybe there's an issue with a lot of rows?
 
Upvote 0
Ah, I see the issue/difference.

I was assuming that the "*" was a wildcard, and they were just looking for "UAT" or "DEV" in the string anywhere.
 
Upvote 0

Forum statistics

Threads
1,203,060
Messages
6,053,303
Members
444,650
Latest member
bookendinSA

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