Delete Column having specific word/value

S

SANDIND

Hi All,

I wanna help writing a Macro that can delete the whole Column if that column
has anywhere any specific word/value for eg ."Delete".

I want to delete all such Columns in the sheet which contain anywhere
"delete".

Please help me on this . SS
 
P

PBezucha

One of many ways:

Option Explicit
Sub DeleteColumsWithKeyword()
' Deletes entire columns if they in the s e l e c t e d range contain
Keyword
Dim Keyword As String, DefaultKeyword As String
Dim S As Range, R As Range, I As Long, J As Long, N As Long
Const Title As String = "Deleting all columns with Keyword"
Set S = Selection
DefaultKeyword = "Delete"
Keyword = InputBox("Keyword", Title, DefaultKeyword)
If Keyword = "" Then Exit Sub
N = S.Columns.Count
For I = N To 1 Step -1
Set R = S.Columns(I)
J = 0
On Error Resume Next
J = WorksheetFunction.Match(Keyword, R, 0)
If J > 0 Then S.Columns(I).Delete
Next I
End Sub

Regards
 
P

PBezucha

Sorry for a mistake in the 3rd row from the end:

Option Explicit
Sub DeleteColumsWithKeyword()
' Deletes entire columns if they in the s e l e c t e d range contain
Keyword
Dim Keyword As String, DefaultKeyword As String
Dim S As Range, R As Range, I As Long, J As Long, N As Long
Const Title As String = "Deleting all columns with Keyword"
Set S = Selection
DefaultKeyword = "Delete"
Keyword = InputBox("Keyword", Title, DefaultKeyword)
If Keyword = "" Then Exit Sub
N = S.Columns.Count
For I = N To 1 Step -1
Set R = S.Columns(I)
J = 0
On Error Resume Next
J = WorksheetFunction.Match(Keyword, R, 0)
If J > 0 Then S.Columns(I).EntireColumn.Delete
Next I
End Sub
 
T

The Code Cage Team

I think you will find this a lot quicker:


Code:
--------------------
Sub Delete_Columns()
Dim I As Long
With ActiveSheet
For I = .UsedRange.Columns.Count To 1 Step -1
If Application.WorksheetFunction.CountIf(.Columns(I), "Delete") Then
.Columns(I).Delete
End If
Next
End With
End Sub
--------------------


--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)
 
S

SANDIND

PBezucha, thanks for your help !!

SS

PBezucha said:
Sorry for a mistake in the 3rd row from the end:

Option Explicit
Sub DeleteColumsWithKeyword()
' Deletes entire columns if they in the s e l e c t e d range contain
Keyword
Dim Keyword As String, DefaultKeyword As String
Dim S As Range, R As Range, I As Long, J As Long, N As Long
Const Title As String = "Deleting all columns with Keyword"
Set S = Selection
DefaultKeyword = "Delete"
Keyword = InputBox("Keyword", Title, DefaultKeyword)
If Keyword = "" Then Exit Sub
N = S.Columns.Count
For I = N To 1 Step -1
Set R = S.Columns(I)
J = 0
On Error Resume Next
J = WorksheetFunction.Match(Keyword, R, 0)
If J > 0 Then S.Columns(I).EntireColumn.Delete
Next I
End Sub
Click to expand...
 
Top