Conditional DeleteRow, if portion of string found
 

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

Conditional DeleteRow, if portion of string found
 

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

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



CLR wrote:

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

Conditional DeleteRow, if portion of string found
 

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

--
Gary''s Student - gsnu200735

Conditional DeleteRow, if portion of string found
 

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

Vaya con Dios,
Chuck, CABGx3



"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200735

Conditional DeleteRow, if portion of string found
 

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

Vaya con Dios,
Chuck, CABGx3


"Dave Peterson" wrote:

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

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



CLR wrote:

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

Conditional DeleteRow, if portion of string found
 

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.

CLR wrote:

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

Vaya con Dios,
Chuck, CABGx3

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200735

--

Dave Peterson

Conditional DeleteRow, if portion of string found
 

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



"Dave Peterson" wrote:

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.

CLR wrote:

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

Vaya con Dios,
Chuck, CABGx3

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200735

--

Dave Peterson

Conditional DeleteRow, if portion of string found
 

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?
--
thx for any help.... and again, wow.
dantee.


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200735