Delete Column having specific word/value

  • Thread starter Thread starter SANDIND
  • Start date Start date
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
 
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
 
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
 
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...
 
Back
Top