InStr Function Question

[gfh28105]

I am trying to use the InStr function to determine if a text string i
contained within a cell, then delete the entire row if it is not found
move down 1 row, and loop until a blank cell is encountered.

It looks as if the syntax requires the text to be typed in the string
field, but I have different text in each cell that I want the functio
to run on.

InStr([start, ]string1, string2[, compare])

Can I put a range for string1

 

[Norman Jones]

Hi GFH,

Sub Tester02()
Dim Pos As Long

ActiveCell.Value = "Big Ben"
pos = InStr(1, ActiveCell.Value, "Ben", vbTextCompare)
MsgBox Pos

End Sub

 

[A C]

I am trying to use the InStr function to determine if a text string is
contained within a cell, then delete the entire row if it is not found,
move down 1 row, and loop until a blank cell is encountered.

It looks as if the syntax requires the text to be typed in the string1
field, but I have different text in each cell that I want the function
to run on.

InStr([start, ]string1, string2[, compare])

Can I put a range for string1?

Just use the <cell>.Value to get the string in the cell. Post back your
looping code so we can see how you are getting to the cell in question and
we can be more precise about the syntax. As an interim I have posted below
some simple demo code which may be enough to help you. Note that if you
delete a row you need to be careful about moving to the next row as that is
now the *next* row...

Sub DeleteRows()

dim strSearch as string
strSearch = "b" 'search for any cell with the letter b in it

Range("A1").Activate
While ActiveCell.Value <> ""
If InStr(ActiveCell.Value, strSearch) Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1).Activate
End If
Wend


End Sub

Hope this helps
A

 

[Juan Pablo González]

Altough you could use the AutoFilter to do this in a much faster way, using
InStr would be something like

If InStr(1, Cells(i, 1).Value, "test") > 0 Then
'The cell has the text
End If

Where i is the counter that you're using (if you are). In that case, it
would look at column A.

But again, try recording a macro using an Autofilter, with "Custom", and
then "Contains" and your text.