Need Excel Macro to Find Specific Text and Characters
 

Hello,
I have a macro created to delete rows that do not contain certain text in a
specific column. However, it is only picking up the cells with that exact
search string, like "KG". How do I get the macro to recognize the other cells
(e.g. "KG-12, "KG-10, PK-6) ? Seems like I need a wildcard for the characters
after the "KG"...The coding is listed below. Thank you!

Dim theRange As Range
Dim lastRow&, firstRow&, x&
Set theRange = ActiveSheet.UsedRange
lastRow = theRange.Cells(theRange.Cells.Count).Row
firstRow = theRange.Cells(1).Row
For x = lastRow To firstRow Step -1
If Not Cells(x, 22) = "'PK-" And Not Cells(x, 22) = "'KG-" And Not Cells(x,
22) = "'EE-" And Not Cells(x, 22) = "'KG" Then
Rows(x).Delete
End If
Next

Need Excel Macro to Find Specific Text and Characters
 

Try this version:

Dim theRange As Range, Str As String
Dim lastRow&, firstRow&, x&
Set theRange = ActiveSheet.UsedRange
lastRow = theRange.Cells(theRange.Cells.Count).Row
firstRow = theRange.Cells(1).Row
For x = lastRow To firstRow Step -1
Str$ = Left(Cells(x, 22).Value, 2)
Select Case Str$
Case "EE", "KG", "PK"
Rows(x).Delete
Case Else
'do nothing
End Select
Next
Set theRange = Nothing

Hope this helps,

Hutch

"TechEd" wrote:

Hello,
I have a macro created to delete rows that do not contain certain text in a
specific column. However, it is only picking up the cells with that exact
search string, like "KG". How do I get the macro to recognize the other cells
(e.g. "KG-12, "KG-10, PK-6) ? Seems like I need a wildcard for the characters
after the "KG"...The coding is listed below. Thank you!

Dim theRange As Range
Dim lastRow&, firstRow&, x&
Set theRange = ActiveSheet.UsedRange
lastRow = theRange.Cells(theRange.Cells.Count).Row
firstRow = theRange.Cells(1).Row
For x = lastRow To firstRow Step -1
If Not Cells(x, 22) = "'PK-" And Not Cells(x, 22) = "'KG-" And Not Cells(x,
22) = "'EE-" And Not Cells(x, 22) = "'KG" Then
Rows(x).Delete
End If
Next