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

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
--
Petr Bezucha


"SANDIND" wrote:

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

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
--
Petr Bezucha


"PBezucha" wrote:

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
--
Petr Bezucha


"SANDIND" wrote:

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

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)
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=34868

PBezucha, thanks for your help !!

SS

"PBezucha" wrote:

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
--
Petr Bezucha


"PBezucha" wrote:

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
--
Petr Bezucha


"SANDIND" wrote:

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