Find last cell that contains string

[Majawat]

I have a sorted list of values. In column B I have values in a few formats:
"XXoz Draft"
"XXoz Bottle"
"XXoz Can"

(where XX is a number)

It's sorted by a =find("Draft",B1) column on C so that all the draft entries are on top.

What I'm looking for is a VBA script to delete all the remaining rows. That is, anything that doesn't have a "Draft" in column B, poof.

Any help would be most appreciated, thanks!

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Not LCase(Range("b" & i).Value) Like "*draft*" Then Rows(i).Delete
Next i
End Sub

 

[Majawat]

Absolutely awesome VoG, thank you very much!

Question out of curiosity, why do you go backwards through the list? That is, wouldn't this also work:

Sub test()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Not LCase(Range("b" & i).Value) Like "*draft*" Then Rows(i).Delete
Next i
End Sub

Also, what does the xlUp in the .End() do/mean?

Thanks again!

 

[Jonmo1]

perhpas Vog is on a break...lol

.End(xlup) simulates pressing CTRL + UP on your keyboard..
To see it in action, put some data in your sheet, say column A.
Then select the last cell at the very bottom of A (A65536 in XL2003)
The press CTRL + UP



Now, why go backwards.
You'll need your thinking cap for this one.

If you go forwards (top to bottom)
For i = 1 To LR

Say row 10 meets the criteria, and the row is deleted.
Whatever WAS in row 11 is now in row 10
Whatever WAS in row 12 is now in row 11
etc..
But the loop doesn't care about that
It then moves on to test row 11 (which now contains data originally in row 12)
The data that was originally in Row 11 got skipped.

Going backwards (bottom to top)
For i = LR To 1 Step -1
Eliminates that problem

Hope that clears it up.


p.s. This is why you can't do

For Each C In Range("A:A")
    If blah blah Then C.EntireRow.Delete
Next C

Because that structure goes top to bottom.

 

[Majawat]

Jonmo1, thanks! That cleared it up a bunch for me.