Del row based on cell value

[wmtsub]

I would like to search column "D" and whenever it finds the word "Gone" delete that row.
Why does this not work?
It runs but does nothing.

Sub DeleteOnD()


'Application.ScreenUpdating = False

For LR = Range("D" & Rows.Count).End(xlUp).Row To 2 Step -1
If Range("D" & LR).Value = "Gone" Then Rows(LR).EntireRow.Delete
Next LR


'Application.ScreenUpdating = True
End Sub

 

[Joe4]

Are the entries in column D EXACTLY equal to "Gone", or is "Gone" just one word in a longer string?

 

[wmtsub]

Just the one word

 

[vcoolio]

Hello wmtsub,

I've just tested your code a few times and it works just fine for me. Is it definitely Column D in which the criteria "Gone" is placed?

If it still doesn't work for you, try the following method:-

Sub DeleteIt()

Application.ScreenUpdating = False

With Sheet1.[A1].CurrentRegion   '---->Change Sheet1 to your sheet name or actual sheet code.
           .AutoFilter 4, "Gone"
           .Offset(1).EntireRow.Delete
           .AutoFilter
End With


Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[LFKim2018]

i would like to search column "d" and whenever it finds the word "gone" delete that row.
Why does this not work?
It runs but does nothing.

Sub deleteond()


'application.screenupdating = false

for lr = range("d" & rows.count).end(xlup).row to 2 step -1
if range("d" & lr).value = "gone" then rows(lr).entirerow.delete
next lr


'application.screenupdating = true
end sub

it could be a case of .. "case sensitive"

 

[wmtsub]

Thanks will give this a try

 

[wmtsub]

How can I protect against case sensitivity?

 

[Fluff]

Use this

If LCase(Range("D" & lr).Value) = "gone" Then Rows(lr).EntireRow.Delete

 

[vcoolio]

Hello wmtsub,

In my code in post #4 , cases are irrelevant as using autofilter won't discriminate.

With the code in your opening post, place this:-

Option Compare Text

directly above your sub name (Sub DeleteOnD())

I hope that this helps.

Cheerio,
vcoolio.

 

[wmtsub]

thanks guys will try