How to Delete Columns with Specific Text in Excel (3 Methods)

To demonstrate our methods we’ll use the dataset below, namely a Monthly Itinerary for a Group of People.

We used the Microsoft Office 365 version here, but you may use any other version at your convenience.

Method 1 – Using Find & Replace Feature

Let’s delete the column that contains Ohio using Excel’s dedicated Find & Replace feature.

Steps:

Go to Home > Find & Select > Find.

A Find and Replace dialog box will appear.

In the Find What box, type the word you are looking for (Ohio).
Click Find All.
Press Ctrl + A to select all the values that are returned.
Click Close.

All the cells containing Ohio (here two cells contain the word) will be selected as below.

Select Home > Delete > Delete Sheet Columns.

All the columns containing Ohio are deleted.

Method 2 – Using Keyboard Shortcut

Steps:

Select all cells containing Ohio by holding down the Ctrl key while clicking on them.
Right-click on your mouse.
Select Delete from the Context Menu.

A small Delete dialog box will appear.
Select Entire column.
Click OK.

The output is given below.

Method 3 – Using VBA Code

This method is the most efficient for larger datasets.

Steps:

Go to the Developer tab > Click on Visual Basic.

Go to the Insert tab > Select Module.

Copy and Paste the following VBA code into the Module window:

Sub DeleteColumnswithSpecificValue()
Dim cell As Range
For Each cell In Range("B2:F9")
If cell.Value = "Ohio" Then
cell.EntireColumn.Delete

End If

Next cell
End Sub

Close the VBA window.

⚡ Code Breakdown:

We select the lookup range.
Then we iterate through the range, and delete the Entire Column containing any cells which match the criterion Ohio.

Go to Developer > Macros.

This will open the Macro Name dialog box.

Select our function (DeleteColumnswithSpecificValue).
Click the Run button.

The final output is given below.

How to Delete Multiple Columns That Are Blank in Excel

Steps:

Select the range of data.

Go to Home > Find & Select > Go To Special.

Select Blanks and click OK.

All the blank cells will be selected.

Click Home > Delete > Delete Sheet Columns.

The result is below.

How to Delete Rows Without Specific Text in Excel

Steps:

Press Alt + F11 to open the Microsoft Visual Basic window.
Go to Insert > Module to open a blank module.

Copy and paste the following code into the module:

Sub DelRowsNotContainSpecificTexr()
    Set findRange = Application.Selection
    Set findRange = Application.InputBox("Select one Range which contain texts that you want to delete rows based on", "DelRowsNotContainCertainText", findRange.Address, Type:=8)
    findTexr = Application.InputBox("Please type a certain text", "DelRowsNotContainSpecificTexr", "", Type:=2)
    For i = findRange.Rows.Count To 1 Step -1
        Set findRow = findRange.Rows(i)
        Set findCell = findRow.Find(findTexr, LookIn:=xlValues)
        If findCell Is Nothing Then
        findRow.Delete
        End If
    Next
End Sub