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:
- Amount Owing
Open the Excel sheet that needs to be queried.
Press Alt + F11 or otherwise go to the Developer ribbon and choose the “Visual Basic” icon if the Developer Tab is enabled.
In the Visual Basic for Applications toolbar, go to Insert > Module
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
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.
Press F5 to run the macro. All columns in the worksheet will be deleted except those named in the script.
2 responses to “VBA: Delete all Excel columns except those named”
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.
How do you do this for multiple worksheets in a workbook? I have 3 tabs that I want to delete columns except those stated.