Need Excel Macro to Find Specific Text and Characters

T

TechEd

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
 
T

Tom Hutchins

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

automatic page break 1
R1C1 1
R1C1 0
HELP Copy And Paste 7
end it 2
Newb: Asking about a tweak in code 2
Paste Special throwing an error 0
Subtotal multiple columns and ... 0

Top