VBA code to delete rows that contain certain text- how can I make this faster/better?

solved

i'm trying to clear out any rows that contain the word "total," but "total" appears across many columns.

as it stands I'm using the below code, just modified to go through each column (6 in total), and it takes forever.

how can I speed this up? there has to be a much better way than how i'm doing it...

Sub test()

lastrow = Cells(Rows.Count, "G").End(xlUp).Row

For i = lastrow To 1 Step -1

If Range("G" & i).Value = "Total" Then

Range("G" & i).EntireRow.Delete

End If

Next i

For i = lastrow To 1 Step -1

If Range("F" & i).Value = "Total" Then

Range("F" & i).EntireRow.Delete

End If

Next i

sub

and i basically copy that block of code 4 more times and just change out the column number

edit: sorry about the formatting

Posted Jul 10, 2019, 3:05 PM UTC

49
sooka33•

I rewrote the delete function, I came up with something very basic that may be really possible to optimize more.
The test data is composed by 10.000 rows with random numbers from column A to column F. Every time the number is below a certain threshold I assign the value "Total" to the cell.
In the end the script needed to delete 9.461 rows, leaving only 539 rows on the sheet.
It took between 5 and 6 seconds to do it, here is the code:

Function DeleteTotal()
    Dim theSht As Worksheet
    Set theSht = ActiveSheet
            
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    For i = 1 To 6
        For j = theSht.UsedRange.Rows.Count To 1 Step -1
            If theSht.Cells(j, i).Value2 = "Total" Then
                theSht.Rows(j).Delete
            End If
        Next j
    Next i
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Function
3
Fapper_KeeperOP•

solution verified

2
Clippy_Office_AsstMOD•

You have awarded 1 point to sooka

^I ^am ^a ^bot, ^please ^contact ^the ^mods ^for ^any ^questions.

1
talltime107•

The fastest way to do this is with the range find method.

1
sooka33•

Sorry for the late reply, if you have a working snippet of code that implements your idea I'll gladly time it for comparison.

1
Fapper_KeeperOP•

this one worked great...is there any way i can assign this to a button?

edit: nm, i got it working

1
sooka33•

Nice!

1
Citanaf35•
Option Explicit
Sub delRows()

Dim ws As Worksheet
Dim rng As Variant
Dim colArr As Variant
Dim j As Integer
Dim i As Long
Dim lastRow As Long

'Vars
colArr = Array("G", "F", "H", "I", "J", "K")  'Replace with the columns you want to modify
Set ws = ActiveSheet

With ws
    'Last row is defined as first column of array
    lastRow = Cells(Rows.Count, colArr(0)).End(xlUp).Row
    
    'Loop across and down
    For j = LBound(colArr) To UBound(colArr)
        For i = lastRow To 1 Step -1
            If Cells(i, colArr(j)).Value = "Total" Then
                'Create a range to delete
                On Error Resume Next
                If rng Is Nothing Then
                    Set rng = Rows(i)
                Else
                    Set rng = Union(rng, Rows(i))
                End If
            End If
        Next i
    Next j
    
    'Delete the Rows
    rng.EntireRow.Delete Shift:=xlUp

End With

End Sub
1
sooka33•

Your solution is nice and give a level of flexibility to the user, unfortunately it's also a bit slow due to many unnecessary iterations with For i = lastRow To 1 Step -1, if a row in the first column of the array is already merged in the rng you shouldn't loop over it again on the remaining n columns (5 in this case); that's why I used UsedRange.Rows.Count, in this way I don't loop through everything for every column.

1