Deleting Columns with String

[scott]

The sub below deletes columns containing certain words or strings. My only
problem is if I pass a "*". If I have a cell has a "*", the below code
deletes almost all columns.

Can someone help me modify it so it will only effect cells with an asterisk?


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

 

[Jim Thomlinson]

Astrisk is a special character. To delete Astrisk rows you will need to pass
in a tilde character ahead of the astisk...

"~*" instead of just "*"

HTH

 

[Tom Ogilvy]

Sub Main()
DeleteColumnswString "*"
End Sub


Sub DeleteColumnswString(ByVal sString As String)

Dim LastCol As Long
Dim r As Long
If sString = "*" Then _
sString = "~*"
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