VBA: Delete all Excel columns except those named

Use this simple VBA script to delete all columns in a worksheet except those that are specifically named.

This Visual Basic for Applications (VBA) script may be useful where large worksheets need to be “cleaned up” and reduced in size. As an example, the following script will delete all columns in the active sheet except those named:

• Date
• Name
• Amount Owing
• Balance

Instructions

Step 1

Open the Excel sheet that needs to be queried.

Step 2

Press Alt + F11 or otherwise go to the Developer ribbon and choose the “Visual Basic” icon if the Developer Tab is enabled.

Step 3

In the Visual Basic for Applications toolbar, go to Insert > Module

Inserting a module in Visual Basic for Applications.

Step 4

Insert the following code into the module:

Sub DeleteSelectedColumns()
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 preserve the column
    Select Case columnHeading
	'Insert name of columns to preserve
        Case "Date", "Name", "Amount Owing", "Balance"
            'Do nothing
        Case Else
            'Delete the column
            ActiveSheet.Columns(currentColumn).Delete
        End Select
    Next
End Sub

Step 5

Customise the script by deleting the four example headings ("Date", "Name", "Amount Owing", "Balance") and adding a list of actual headers to preserve. As many header names can be added as is required.

Step 6

Press F5 to run the macro. All columns in the worksheet will be deleted except those named in the script.

Comments

2 responses to “VBA: Delete all Excel columns except those named”

On 23 November 2016, Breko90 wrote:

This is awesome, you have no idea how much time this saves me in creating the database for my master-thesis. However, I have 40 firms (all different excel files), and a longitude database (for 10 years), meaning that I have to perform this code 400 times. Is there a way to to this faster? Something like, doing all the 40 firms for year 1 at once? That would be even more awesome.

Thanks,

Breko

Reply

On 10 January 2019, Tia wrote:

How do you do this for multiple worksheets in a workbook? I have 3 tabs that I want to delete columns except those stated.

Reply

Have Your Say

Δ