Delete all columns containing a specific word?

samilynn

Board Regular
Joined
Jun 24, 2003
Messages
158
Office Version
  1. 2016
Platform
  1. Windows
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. :(
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Record a MACRO to apply a filter based on your word then you can look to delete the columns from there.
 
Upvote 0
Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
This seems to work for me.

Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top