How to Delete Columns Based on Header Using VBA in Excel

In general, Excel workbooks contain numerous columns. Sometimes, by the time some columns become redundant. So, we need to delete those columns. Excel VBA delete columns based on header with ease.

Let’s say, we have a dataset where sold Products are displayed with Order Date, City, Category, Quantity, and Unit Price. By time one or two columns become redundant such as City or Category as a result we want to delete them.

dataset-Delete Columns Based On Header Using VBA in Excel

In this article, we demonstrate different variants of Excel VBA to delete columns based on header.


Download Excel Workbook


⧭ 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 mainly 3 ways to open Microsoft Visual Basic window.

🔼 Using Keyboard Shortcuts

Press ALT+F11 altogether to open Microsoft Visual Basic window.

🔼 Using Developer Tab

In an Excel worksheet, Go to Developer Tab > Select Visual Basic. The Microsoft Visual Basic window appears.

Developer Tab-Delete Columns Based On Header Using VBA in Excel

🔼 Using Worksheet Tab

Go to any worksheet, Right-Click on it > Choose View Code (from the Context Menu).

worksheet options

🔄 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) > then Choose Module.

Sheet's option

 🔼 You can also do it by Selecting Insert (from the Toolbar) > then Choosing Module.

module insertion

 


6 Easy Ways to Delete Columns Based on Header Using VBA in Excel

Method 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. Then we execute a macro code to delete specific columns from the range.

method-1-1-Delete Columns Based On Header Using VBA in Excel

Step 1: Open Microsoft Visual Basic then insert a Module using the instruction section. 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

method-1 macro

➤ 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.

Step 2: Press F5 to run the macro. After returning to the workbook, you see the specific column is deleted as depicted in the following picture.

method-1 result

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 (8 Examples)


Method 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 Excel 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 from 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.

method-2-1-Delete Columns Based On Header Using VBA in Excel

Step 1: Repeat the instruction section to open and insert 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

method-2 macro

➤ 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 then deletes it. Afterward, the macro looks into other worksheets and matches the column name (i.e., City) at row 3. If a similar column is found at a similar position the macro deletes them.

Step 2: 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.

method-2 final result

Read More: How to Delete Duplicate Columns in Excel (6 Ways)


Method 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.

method-3-1-Delete Columns Based On Header Using VBA in Excel

Step 1: 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

method-3 macro

➤ 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 step and the VBA InStr function 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.

Step 2: Press F5 to run the macro. Then return to the worksheet, you see the City column gets deleted.

method-3 final result

Related Content: Delete Blank Columns in Excel (3 Ways)


Similar Readings:


Method 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.

method-4-1-Delete Columns Based On Header Using VBA in Excel

Step 1: 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

method-4 macro

➤ From the above image, the code’s sections,

1 – take forward the macro by setting the Sub name.

2 – declare the variable as 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 cell property.

4VBA Case assigns the column names (i.e., City and Category).

5 – delete the assigned columns.

Step 2: 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.

1st time macro execution

➤ 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.

2nd time macro execution

Read More: How to Delete Unused Columns in Excel(Easiest 5 Methods)


Method 5: Deleting Specific Column from a Table

Some real-life scenarios may lead to 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.

method-5-1-Delete Columns Based On Header Using VBA in Excel

Step 1: 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

method-5 macro

➤ The above image has code sections,

1 – take forward the macro by setting the Sub name.

2 – declare 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.

Step 2: To run the macro press F5. After returning to the worksheet, you see the Category column is absent, similar to the following image.

method-5 final result


Method 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.

method-6-1-Delete Columns Based On Header Using VBA in Excel

Step 1: 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

method-6 macro

➤ The code is marked in parts,

1 – begin the macro by setting the Sub name.

2 – assign the active worksheet to count columns from.

3VBA InStr function searches for asterisk (*) in the column’s header. After matching, the VBA delete command deletes the column.

Step 2: Push the F5 key to run the macro. Then back to the worksheet, you see both asterisk (*) containing columns get deleted.

method-6 final result

Related Content: How to Delete Columns in Excel Without Affecting Formula (Two Ways)


Conclusion

In this article, we’ve demonstrated multiple macro variants of Excel VBA to delete columns based on 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.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo