I'm deleting a column if a cell contains the variable "sString" below. How
can I modify this sub so it will find the string in a cell even if it has
other characters in the cell besides "sString"? Basically, I need to check
if "sString" is in a cell, regardless of other words or spaces.

any help?

Sub DeleteColumnswString(ByVal sString As String)

Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xlToLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIf(Columns(r), sString) < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True

End Sub

Try this...

Sub DeleteRows(ByVal strToFind As String)
Dim rngFound As Range

Set rngFound = Cells.Find(strToFind, , , xlPart, , , False)
Do While Not rngFound Is Nothing
rngFound.EntireColumn.Delete
Set rngFound = Cells.Find(strToFind, , , xlPart, , , False)
Loop

End Sub

HTH

"scott" wrote:

I'm deleting a column if a cell contains the variable "sString" below. How
can I modify this sub so it will find the string in a cell even if it has
other characters in the cell besides "sString"? Basically, I need to check
if "sString" is in a cell, regardless of other words or spaces.

any help?

Sub DeleteColumnswString(ByVal sString As String)

Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xlToLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIf(Columns(r), sString) < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True

End Sub

if you want to delete the column if the sString is found anywhere in the
column: (even as a substring) then
Sub DeleteColumnswString(ByVal sString As String)

Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xlToLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIf(Columns(r), "*" & sString & "*") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True

End Sub

If you want to delete the column if sString is found anywhere in the cell in
the first row of that column (even as a substring) then

Sub DeleteColumnswString(ByVal sString As String)

Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xlToLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIf(Cells(1,r), "*" & sString & "*") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True

End Sub

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
I'm deleting a column if a cell contains the variable "sString" below. How
can I modify this sub so it will find the string in a cell even if it has
other characters in the cell besides "sString"? Basically, I need to check
if "sString" is in a cell, regardless of other words or spaces.

any help?

Sub DeleteColumnswString(ByVal sString As String)

Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xlToLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIf(Columns(r), sString) < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True

End Sub

using your method:

Public Sub DeleteColumnswString(ByVal sString As String)
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xlToLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIf(Columns(r), "*" & sString & "*") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub



You might also look at VBA's Find method, using LookAt:=xlPart


In article ,
"scott" wrote:

I'm deleting a column if a cell contains the variable "sString" below. How
can I modify this sub so it will find the string in a cell even if it has
other characters in the cell besides "sString"? Basically, I need to check
if "sString" is in a cell, regardless of other words or spaces.

any help?

Sub DeleteColumnswString(ByVal sString As String)

Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xlToLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIf(Columns(r), sString) < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True

End Sub