Delete Column Containing String

[scott]

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

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[JE McGimpsey]

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