Loop that will Delete all rows where a cell Dosn't contain "i" in string

[mattd4385]

Col A contains no blanks

Col F contains Blanks and random string

Col F:
i
h
A

iw
LA
tcpi

tmi

How can i create a code to loop throught col F and remove all of the cells that don't contain "i" anywhere in the string. To give the following result

Results:
Col F:
i
iw
tcpi
tmi

 

[texasalynn]

try this

Sub delete_not_i()
Dim LR As Long
LR = ActiveSheet.Range("F" & Rows.Count).End(xlUp).Row 'get last row column A
For i = LR To 2 Step -1
    If InStr(Cells(i, "F"), "i") = 0 Then
        Rows(i).Delete
    End If
Next i
End Sub

 

[mattd4385]

It works great thank you so much for you help

I am still trying to understand how the code knows what the last row was in col A if you never used the letter A in the code.

 

[texasalynn]

no I just used column F to decide on the last row, since that was where you wanted to test for the missing "i"

 

[mattd4385]

so how did it know the last row since the col had blanks

 

[jasonb75]

An observation if you're going to use this code on a regular basis.

If the last row(s) were blank / empty in column F, but held a value in column A, should (and would) the code delete those rows?


edit: ignore that, you already raised the point while I was typing.

 

[texasalynn]

You would change this line

LR = ActiveSheet.Range("F" & Rows.Count).End(xlUp).Row

to this
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row