If Statement with Wildcards not Working

[cr0375]

I'm trying to make delete all rows that contain the text "UASGN" in column J.
I have tried a few different methods based upon research from this site, but
I'm having an issue with each one. The one I think I'll use is this:

For i = 1 To 672
If Cells(i, "J") = "*UASGN*" Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1 ' as we just deleted a row, we should not increase i
End If
Next i

When I use this, it appears to have a problem with the wildcards. If I
remove the wildcards and replace with a finite character(s), it works.

Thanks.

 

[JP Ronse]

Hi

Try

If Cells(i, "J") LIKE "*UASGN*" Then


Wkr,

JP

 

[Jacob Skaria]

Try the below

For i = 672 To 1 Step -1
If Cells(i, "J") Like "*UASGN*" Then Rows(i & ":" & i).Delete
Next i

If this post helps click Yes

 

[Dave Peterson]

Another way to do it without looping through all the cells in the range is to
use .Find().

Then you just find, delete, find, delete, ... until there isn't any more left.

In code:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FoundCell As Range
Dim FindWhat As String

Set wks = ActiveSheet

FindWhat = "uasgn"

With wks.Range("J:j")
Do
Set FoundCell = .Cells.Find(what:=FindWhat, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
'they're all gone, get out of the loop
Exit Do
Else
'delete the entire row
FoundCell.EntireRow.Delete
End If
Loop
End With

End Sub

The xlpart is important (like your *uasgn* comparison).

I used matchcase:=false. You may not want that.

Another way to do it manually if you don't want to run the macro.

Apply data|filter|autofilter to column J:
filter to show the cells/rows that contain UASGN.
Delete those visible rows
(you may need to select the range, hit F5, special|visible cells only, then
delete them.)

Another way (depending on the version of excel you're using):

Select column J
Edit|Find
what: uasgn
(in values and not matching the entire cell)

Hit Find All (if you have that button on the Find dialog)

Select one of the lines in that listbox and hit ctrl-a to select them all.

Then delete the rows that are still selected.
(rightclick on one and choose delete, entire row)

 

[cr0375]

thanks both of you

Hi

Try

If Cells(i, "J") LIKE "*UASGN*" Then


Wkr,

JP

 

[cr0375]

Helpful, Thanks.

Try the below

For i = 672 To 1 Step -1
If Cells(i, "J") Like "*UASGN*" Then Rows(i & ":" & i).Delete
Next i

If this post helps click Yes

 

[cr0375]

Helpful, thanks!

Another way to do it without looping through all the cells in the range is to
use .Find().

Then you just find, delete, find, delete, ... until there isn't any more left.

In code:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FoundCell As Range
Dim FindWhat As String

Set wks = ActiveSheet

FindWhat = "uasgn"

With wks.Range("J:j")
Do
Set FoundCell = .Cells.Find(what:=FindWhat, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
'they're all gone, get out of the loop
Exit Do
Else
'delete the entire row
FoundCell.EntireRow.Delete
End If
Loop
End With

End Sub

The xlpart is important (like your *uasgn* comparison).

I used matchcase:=false. You may not want that.

Another way to do it manually if you don't want to run the macro.

Apply data|filter|autofilter to column J:
filter to show the cells/rows that contain UASGN.
Delete those visible rows
(you may need to select the range, hit F5, special|visible cells only, then
delete them.)

Another way (depending on the version of excel you're using):

Select column J
Edit|Find
what: uasgn
(in values and not matching the entire cell)

Hit Find All (if you have that button on the Find dialog)

Select one of the lines in that listbox and hit ctrl-a to select them all.

Then delete the rows that are still selected.
(rightclick on one and choose delete, entire row)

 

[Ashish Mathur]

Hi,

You may also try this. Select the column and press Ctrl+F. In the find
what box, type UASGN and then click on find All. Now press Ctrl+A to
highlight all the cell which have UASGN. You may now delete them

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com