In general, Excel workbooks contain numerous columns. Sometimes, by the time some columns become redundant. So, we need to delete those columns. We can delete columns based on the header using Excel VBA with ease.
Let’s say, we have a dataset where sold Products are displayed with Order Date, City, Category, Quantity, and Unit Price. By the time one or two columns become redundant such as City or Category as a result we want to delete them.
In this article, we demonstrate different variants of Excel VBA to delete columns based on the header.
⧠Opening Microsoft Visual Basic and Inserting Code in the Module
Before proceeding to demonstrate any methods, it’s necessary to know the ways to open and insert a Module in Microsoft Visual Basic in Excel.
🔄 Opening Microsoft Visual Basic: There are 3 ways to open Microsoft Visual Basic window.
🔼 Using Keyboard Shortcuts
Press ALT+F11 altogether to open the Microsoft Visual Basic window.
🔼 Using Developer Tab
In an Excel worksheet, go to the Developer tab > Select Visual Basic. The Microsoft Visual Basic window appears.
🔼 Using Worksheet Tab
Go to any worksheet, right-click on it > Choose View Code from the Context Menu.
🔄 Inserting a Module in Microsoft Visual Basic: There are 2 ways to insert a Module in Microsoft Visual Basic window,
🔼 After opening the Microsoft Visual Basic window, select a worksheet > Right-click on it > Select Insert from the Context Menu > Choose Module.
 🔼 You can also do it by selecting Insert from the Toolbar > Choose Module.
How to Delete Columns Based on Header Using VBA in Excel: 6 Ways
1. Excel VBA to Delete Specific Column from a Range Based on Header
From the dataset (shown below), we know that our worksheet contains multiple columns. Some of the columns (i.e., City or Category) become redundant over time. So, we assign a range (i.e., B4:G16) where our columns reside. Execute a macro code to delete specific columns from the range.
- Open Microsoft Visual Basic then insert a Module.
- Paste the following macro in any Module.
Sub Delete_Specifc_Column()
Set Data = Range("B4:G16")
For Each cell In Data
If cell.Value = "City" Then cell.EntireColumn.Delete
Next
End Sub
➤ In the code,
1 – Start the macro procedure by declaring the Sub name. You can assign any name to the code.
2 – Assign the data to a range (i.e., B4:G16).
3 – Create a loop and assign the cell value to a specific cell name (i.e., City). Then inspect each cell value in the range (i.e., B4:G16) and delete matched columns.
- Press F5 to run the macro. After returning to the workbook, you see the specific column is deleted as depicted in the following picture.
You can assign any column name in the macro and the macro will delete the entire column. Also, you can change or modify the range according to your demand.
Read More: VBA Macro to Delete Columns Based on Criteria in Excel
2. Deleting Similar Columns from All Worksheets Using VBA
In method 1, we deleted specific columns from a range. If we want, we can add a dimension to the VBA macro that deletes similar columns (by name and position) from all the worksheets. Though for convenience we just demonstrate one particular worksheet execution for the macro. However, the macro deletes similar columns by name and position from all worksheets in a workbook.
Our dataset has columns on row 3 and we want to delete the City column. If the City column exists in other worksheets at row 3, the macro is capable of deleting that also.
- Repeat the instruction section to open and insert a Module in Microsoft Visual Basic.
Option Explicit
Sub Delete_SPColumn_fromAllWS()
Dim wrksht As Worksheet
Dim Rng As Range
For Each wrksht In ActiveWorkbook.Worksheets
Do
Set Rng = wrksht.Rows(3).Find(What:="City", LookIn:=xlValues, lookat:=xlPart)
If Not Rng Is Nothing Then
Rng.EntireColumn.Delete
End If
Loop While Not Rng Is Nothing
Next
End Sub
➤ From the above image, in the sections,
1 – Begin the macro code declaring the VBA macro code’s Sub name.
2 – Declare the variables as Worksheet and Range.
3 – Create a loop where the macro finds the City column at row 3 and then deletes it. Afterward, the macro looks into other worksheets and matches the column name (i.e., City) in row 3. If a similar column is found at a similar position the macro deletes them.
- Hit F5 to run the macro and it deletes the City column from each worksheet containing the particular column. However, you have no other worksheet with the same column name (i.e., City) at the same position (i.e., row 3). As a result, you see only one worksheet from where the specific column gets deleted.
3. VBA to Delete Specific Column from Specific Worksheet
Suppose, we want to delete a specific column from a specific worksheet. Excel VBA macros also offer macro variants to deal with it. The VBA InStr function provides a start position and strings that are to be searched. Then, a simple VBA delete command can delete the entire column.
We have columns started at row 4 as shown in the below picture.
- Type the below macro code in the Module (inserted by following the instruction section).
Option Explicit
Sub Delete_SPColumn_fromSPWS()
Dim i As Long, cell As Long
cell = Cells(4, Columns.Count).End(xlToLeft).Column
With Sheets("Sale").Cells(4, Columns.Count).End(xlToLeft).Column
For i = cell To 1 Step -1
If InStr(Cells(4, i), "City") > 0 Then
Cells(4, i).EntireColumn.Delete
End If
Next i
End With
End Sub
➤ The code’s sections,
1 – Initiate the macro procedure declaring the Sub name.
2 – Declare the variables as Long.
3 – Assign the cell variable from a particular position (i.e., row 4 and iterative column). And assign a specific worksheet (i.e., Sale).
4 – Create a loop with the step and the VBA InStr function to search the City column in the assigned worksheet. Then VBA deletes the column by a delete command.
The VBA InStr function takes multiple strings. Therefore, you can insert multiple column names to delete them from the specific dataset. For representation, we just delete only one column (i.e., City) from the worksheet.
- Press F5 to run the macro. Then return to the worksheet, and you see the City column gets deleted.
4. Deleting Assigned Columns from Active Worksheet
Now, we want to delete columns from an assigned list. The list contains multiple columns that we desire to delete from the dataset. We can assign the City and Category columns to a list using the VBA Select Case statement. The below depiction shows our dataset.
- Write the subsequent macro in Microsoft Visual Basic Module.
Sub Delete_MultipleCol_List()
Dim i As Integer
For i = 1 To 6 Step 1
Select Case Cells(4, i).Value
Case "City", "Category"
Cells(4, i).EntireColumn.Delete
End Select
Next i
End Sub
➤ From the above image, the code’s sections,
1 – Take forward the macro by setting the Sub name.
2 – Declare the variable as the Integer.
3 – Create a loop of 6 iterations as we have 6 different columns in the dataset. Also starts the execution from row 4 where all the columns start using the cell property.
4 – VBA Case assigns the column names (i.e., City and Category).
5 – Delete the assigned columns.
- In order to run the macro press F5. Pressing F5 for the 1st time deletes the 1st assigned column (i.e., City) from the worksheet.
➤ If you want to delete the 2nd assigned column, hit the F5 again (2nd time). The macro deletes the 2nd assigned column instantly as you can see from the below image.
5. Deleting Specific Column from a Table
Some real-life scenarios may lead to an Excel Table, where data is constructed as a table and we want to delete a specific column. So, let’s say we have a dataset constructed as a table similar to the picture below.
- Use the following macro code in any Module of any Microsoft Visual Basic window.
Sub Delete_ColHeader_Table()
Dim wrkTable As ListObject
Dim wrkSheet As Worksheet
Dim i As Integer
Dim colNum As String
Set wrkTable = ActiveSheet.ListObjects("Table1")
Set wrkSheet = Sheet4
colNum = "Category" 'case sensitive name
For i = 1 To wrkTable.ListColumns.Count
With wrkSheet
If wrkTable.ListColumns(i).Name = colNum Then
wrkTable.ListColumns(i).Delete
Exit For
End If
End With
Next i
End Sub
➤ The above image has code sections,
1 – Take forward the macro by setting the Sub name.
2 – Declar the variables as a ListObject, Worksheet, Integer, and String.
3 – Assign variables such as wrkTable to Table1, wrkSheet to Sheet4 and colNum to Category.
4 – Create a loop to count the Table columns in the worksheet.
5 – Delete the column that matches the colNum.
- To run the macro press F5. After returning to the worksheet, you see the Category column is absent, similar to the following image.
6. Using VBA to Delete Multiple Columns
There can be instances where characters may be included with text in column headers. Also, identical text with different numbers can be used to name the columns. In those cases, this method is pretty handy. In this method, we use an asterisk (*) as a character to name one or two columns as depicted in the following macro.
- Use the following macro in any Module.
Sub Delete_columns_withSPChrctr()
For i = ActiveSheet.Columns.Count To 1 Step -1
If InStr(1, Cells(4, i), "*") Then Columns(i).EntireColumn.Delete
Next i
End Sub
➤ The code is marked in parts,
1 – Begin the macro by setting the Sub name.
2 – Assign the active worksheet to count columns from.
3 – VBA InStr function searches for the asterisk (*) in the column’s header. After matching, the VBA delete command deletes the column.
- Push the F5 key to run the macro. Then back to the worksheet, you see both asterisk (*) containing columns get deleted.
We have deleted multiple columns with asterisk here.
Read More: How to Delete Multiple Columns by Number Using VBA in Excel
Download Excel Workbook
Conclusion
In this article, we’ve demonstrated multiple macro variants of Excel VBA to delete columns based on the header. Each macro we demonstrated has its own convenience depending on the data type. Hope these above-mentioned methods serve your purpose. Comment, if you have further inquiries or have anything to add.
