Delete Every Column Except Some Column In Excel

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.




Excel showing columns to be deleted while keeping some column

Step 1

Open VB editor from Developer tab in excel as shown in the figure.

Open developer tab to write VBA to loop through columns to delete all the columns while keeping some

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.

Insert a module in the VB editor

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

VBA code to delete all columns but keep some columns

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.

all columns are deleted retaining some other columns

Hope this helped.




Share The Knowledge

Random Posts