VBA code to delete rows that contain certain text- how can I make this faster/better?
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
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:
solution verified
You have awarded 1 point to sooka
^I ^am ^a ^bot, ^please ^contact ^the ^mods ^for ^any ^questions.
The fastest way to do this is with the range find method.
Sorry for the late reply, if you have a working snippet of code that implements your idea I'll gladly time it for comparison.
this one worked great...is there any way i can assign this to a button?
edit: nm, i got it working
Nice!