VBA: Delete all Excel columns except those 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.