Delete all columns containing a specific word?

[samilynn]

Is it possible to delete all columns that contain a specific word?

I am trying to create a macro for it, but I'm not having any luck at all.

 

[Trevor G]

Record a MACRO to apply a filter based on your word then you can look to delete the columns from there.

 

[AlphaFrog]

Sub Col_Delete_by_Word()
    
    Dim Found As Range, strWord As String, Counter As Long
    
    strWord = Application.InputBox("Enter the word to search for.", "Delete the columns with this word", Type:=2)
    If strWord = "False" Or strWord = "" Then Exit Sub 'User canceled
    
    Set Found = Cells.Find(strWord, , , xlPart, , xlNext, False)
    
    If Not Found Is Nothing Then
    
        Application.ScreenUpdating = False
        Do
            Found.EntireColumn.Delete
            Counter = Counter + 1
            Set Found = Cells.Find(strWord, , , xlPart, , xlNext, False)
            
        Loop Until Found Is Nothing
        Application.ScreenUpdating = True
        
        MsgBox Counter & " columns deleted.", vbInformation, "Process Complete"
        
    Else
        MsgBox "No match found for: " & strWord, vbInformation, "No Match"
    End If
    
    
End Sub

 

[Trevor G]

I have looked at creating some code as I miss read your request. Change the word in red from Chair to your word, it is case sensitive though.

Sub DeleteColumns()
Dim x As Integer
Dim cell As Range
Dim myRange As Range
Dim myDeleteData As String
Dim myDeleteColumns(256) As Boolean
'create a string of all data that if found, will cause a column delete
myDeleteData = "Chair"
'use goto special to highlight all cells that contain a constant
Cells.SpecialCells(xlCellTypeConstants, 23).Select
Set myRange = Selection
For Each cell In myRange
'search to see if the cell contents is in the list
If InStr(myDeleteData, cell.Value) Then
'if so, then mark it for deletion
myDeleteColumns(cell.Column) = True
End If
Next cell
For x = 256 To 1 Step -1
'delete columns from the right, so you keep integrity of column numbers
If myDeleteColumns(x) = True Then
Columns(x).Delete
End If
Next x
End Sub

 

[andperseandy]

This seems to work for me.

Sub dothis()
Dim c As Range
Dim str As String

str = "searchword"

For Each c In ActiveSheet.UsedRange
    If InStr(c.Value, str) > 0 Then
    c.EntireColumn.Delete shift:=xlToLeft
    End If
Next c

End Sub

 

[Weaver]

Record a MACRO to apply a filter based on your word then you can look to delete the columns from there.

You can filter columns?

 

[manvit]

This seems to work only one time. if the same word is in another column, it does not delete that. so we have to call this macro again if there is another column with same word.

 

[My Aswer Is This]

I suggest you start a new posting and explain in detail what you wanting. This original post is nearly 10 years old.

This seems to work only one time. if the same word is in another column, it does not delete that. so we have to call this macro again if there is another column with same word.

 

[manvit]

Hi My Answer is this ! ,
I did not start this thread :P

Cheers,
Manvit

 

[My Aswer Is This]

Yes I know that.

If you have a question and need help I'm just saying you should start a new posting it would help us know what your needs are. And not have to read back and see what the original question was and the adapt a answer for your needs.

Hi My Answer is this ! ,
I did not start this thread :P

Cheers,
Manvit