Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Columns with String
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Columns with String
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 "scott" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Columns with String
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 -- Regards, Tom Ogilvy "scott" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Columns, Then deleting old columns | Excel Discussion (Misc queries) | |||
Combining Text from 2 Columns into 1 then Deleting the 2 Columns | Excel Worksheet Functions | |||
Deleting numbers in a string | Excel Discussion (Misc queries) | |||
Deleting Columns | Excel Programming | |||
deleting extra spaces in a string | Excel Programming |