Conditional DeleteRow, if portion of string found

[Guest]

Hi All......

I have two macros, the first one works to delete rows if a cell in column H
is empty.

Sub DeleterowsNONAME()
Dim lastrow As Long, r As Long
Application.ScreenUpdating = False
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For r = lastrow To 2 Step -1
If Cells(r, "h") = "" Then
Rows(r).EntireRow.Delete
End If
Next r
Application.ScreenUpdating = True
End Sub

The second one, I'm trying to modify so if the word "trust" is found
anywhere in the cell string, to delete the entire row.....this one don't work.


Sub DeleterowsTRUST()
Dim lastrow As Long, r As Long
Application.ScreenUpdating = False
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For r = lastrow To 2 Step -1
If Cells(r, "h") = "*TRUST*" Then
Rows(r).EntireRow.Delete
End If
Next r
Application.ScreenUpdating = True
End Sub


Any help would be appreciated............using XL97
Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

If Cells(r, "h").value like "*TRUST*" Then
or

if lcase(Cells(r, "h").value) like lcase("*TRUST*") Then

 

[Guest]

Sub DeleterowsTRUST()
Dim lastrow As Long, r As Long
s = "TRUST"
Application.ScreenUpdating = False
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For r = lastrow To 2 Step -1
If InStr(Cells(r, "h").Value, s) > 0 Then
Rows(r).EntireRow.Delete
End If
Next r
Application.ScreenUpdating = True
End Sub

 

[Guest]

Thank you Sir, it works fine.....appreciate the help.

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Thank you Sir......both work fine.......I "like" them.

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

Just to add to Gary's Student's post...

You may want to use
If InStr(1, Cells(r, "h").Value, s, vbtextcompare) > 0 Then
To avoid any problem with case.

 

[Guest]

Thaks Dave...........I just "keep on learning".........the guy in the next
cubicle asked me today, "how often do you learn something new in
Excel".......the answer of course was "EVERY DAY".

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Wow, that is beautiful. You guys make me look like a VBA preschooler, but
that is just fine with me. What would I do to look for a string in the
entire row, instead of just column H, would I do a bunch of "If" statements
or a Select case structure to find the desired string? I.E.,

For r = lastrow To 2 Step -1
If InStr(Cells(r, "h").Value, s) > 0 Then
Rows(r).EntireRow.Delete
End If
If InStr(Cells(r, "i").Value, s) > 0 Then
Rows(r).EntireRow.Delete
End If
If InStr(Cells(r, "j").Value, s) > 0 Then
Rows(r).EntireRow.Delete
End If
Next r

Or Is there a better, more efficient way?