Deleting Columns with String

  • Thread starter Thread starter scott
  • Start date Start date
S

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
 
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
 
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
 
Back
Top