In this post we will see how to delete all the columns in excel except some of the specified columns, we will write a vba code to perform this task and we will specify column names that don’t want to delete in the vba code itself.
Suppose we have excel data with column from A to M and we want to retain column D,F,H and J in the excel data and delete all other columns using excel vba.
Step 1
Open VB editor from Developer tab in excel as shown in the figure.
Step 2
Insert a module in which we will write a VBA code which will delete all the columns in excel in reverse order and keep certain column that we have specified in the VBA program.
Step 3
Now double click the newly created module to open that and paste the below code in the module.
Sub DeleteColumns()
Dim currentColumn As Integer
Dim columnHeading As String
For currentColumn = ActiveSheet.UsedRange.Columns.Count To 1 Step -1
columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value
‘CHECK WHETHER TO KEEP THE COLUMN
Select Case columnHeading
Case “D”, “F”, “H”, “J”
‘Do nothing
Case Else
‘Delete if the cell doesn’t contain “Homer”
If InStr(1, _
ActiveSheet.UsedRange.Cells(1, currentColumn).Value, _
“Homer”, vbBinaryCompare) = 0 Then
ActiveSheet.Columns(currentColumn).Delete
End If
End Select
Next
End Sub
Now run the program by pressing F5 or by pressing play button in the VB editor, this macro will iterate over the columns in reverse order, check the headers in a Select Case, and delete the columns as needed.
Hope this helped.
