Delete rows if (certain value) exists in data set.

[griffindor2020]

Hello,

I've been trying to delete rows in my data set that contain the following keyword "lename"

This is the following script I found and the following error

Script :
Sub Delete_All_Rows_IF_Cell_Contains_Certain_String_Text()
Dim lRow As Long
Dim iCntr As Long
lRow = 150000
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 3).Value = "lename" Then
Rows(iCntr).Delete
End If
Next
End

Error:
Compile Error : Expected End Sub

Let me know what went wrong and what is the best way to fix it

Thank you.

 

[Fluff]

Just add the word Sub after the word End (On the same line)

 

[griffindor2020]

Just add the word Sub after the word End (On the same line)

This is my code now

Sub Delete_All_Rows_IF_Cell_Contains_Certain_String_Text()
Dim lRow As Long
Dim iCntr As Long
lRow = 150000
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 3).Value = "lename" Then
Rows(iCntr).Delete
End If
Next
End Sub

But it hasn't deleted any of the rows with the value "lename".

 

[Fluff]

Does lename appear in col C and is it all lower case?

 

[griffindor2020]

Hey,

lename appears in column D ... should I change If Cells(iCntr, 3).Value = "lename" for If Cells(iCntr, 4).Value = "lename"

?

Let me know !

 

[Fluff]

That's right

 

[griffindor2020]

That's right

Hey !

I modified the script However, my computer is overheating and I think excel has stopped responding :') !

I guess I'll have to wait until it unbugs?

 

[Fluff]

This should be faster than your code.

Sub Delete_All_Rows_IF_Cell_Contains_Certain_String_Text()
   Dim iCntr As Long
   Dim Rng As Range
   
   For iCntr = Range("D" & Rows.Count).End(xlUp).Row To 1 Step -1
      If Cells(iCntr, 3).Value = "lename" Then
         If Rng Is Nothing Then Set Rng = Rows(i) Else Set Rng = Union(Rng, Rows(i))
      End If
   Next iCntr
   If Not Rng Is Nothing Then Rng.Delete
End Sub