Excel VBA code to Delete Entire Column example will help us to delete Columns in excel worksheet. We can use Delete method of Columns to delete the Entire Column. In this example we will see how to delete the entire Column in excel worksheet using VBA. Excel VBA Macro code for deleting entire Columns macro should work for all the version of Microsoft Excel 2003, Excel 2007, Excel 2010, and Excel 2013.
VBA code to Delete Entire Column
Here is the Example VBA syntax and Example VBA Macro code to Delete Entire Column from excel worksheets. This will help you to know how to delete entire Column from Excel workbook using VBA.
: VBA Delete Entire Column: Syntax
Following is the VBA Syntax and sample VBA macro command to Delete Entire Column from worksheet using VBA. We are using the Delete method of the Columns object of worksheet.
Here Column Number is your Column number to delete. And EntireColumn.Delete method will delete the Entire Column from the Excel spreadsheet. You can also use Column Name, instead of Column Number.
Delete Entire Column using VBA: Examples
The following Excel VBA macro code is to delete entire Column from the worksheet. This VBA macro will delete the Column which we have mentioned in the code.
Sub sbVBS_To_Delete_EntireColumn () Columns(1).EntireColumn.Delete End Sub
Instructions to run the VBA Macro code to delete Entire Column
Please follow the below steps to execute the VBA code to delete entire Column.
Step 1: Open any existing Excel workbook
Step 2: Press Alt+F11 – This will open the VBA Editor
Step 3: Insert a code module from then insert menu
Step 4: Copy the above code and paste in the code module which have inserted in the above step
Step 5: enter some sample data in Column 1
Step 6: Now press F5 to execute the code
Now you can observe that the entire Column 1 is deleted from worksheet.
Explained VBA Code to Delete Entire Column:
‘Starting program and sub procedure to write VBA code to delete entire Column from sheet
‘Specifying the Columns to delete and Deleting the Columns using EntireColumn.Delete method.
‘Ending the sub procedure to delete entire Column
Here Columns(1) is to tell excel to delete Column 1 of the worksheet. And Delete method will delete the entire Column form the worksheet.
VBA to Delete Entire Column in Excel :
If you want to delete the first few Columns in excel, you can repeat the delete command several times. The following example will show you how to delete the first 3 Columns in the worksheet.
Sub sbVBS_To_Delete_FirstFewColumns_in_Excel() Columns(1).EntireColumn.Delete Columns(1).EntireColumn.Delete Columns(1).EntireColumn.Delete End Sub
In this example, you can see that the delete command is repeated 3 times. It will first delete the first Column, when we delete the first Column. The next Column becomes (2nd Column) the first Column. So if you delete the first Column again. The next Column (3rd Column) becomes the first Column. That means, we have deleted the first three Columns using VBA.
VBA to Delete Entire Column in Excel – VBA code Explained
Here is the explanation of the Macro to Delete first three Columns in Excel.
‘Starting the procedure to write VBA commands to delete first few (3) Columns
‘First Column is deleting
‘Now Second Column becomes the first Column- again we are deleting the first Column
‘Now Third Column becomes the first Column- again delete command is deleting the first Column
‘We have deleted the first 3 Columns, and ending the sub procedure here.
VBA to Delete Entire Column in Excel : Using For Loop
Here is the example VBA macro to delete the first few Columns using For Loop. This will delete the first 10 records using For loop in VBA.
Sub sbVBS_To_Delete_EntireColumn_For_Loop() Dim iCntr For iCntr = 1 To 10 Step 1 Columns(1).EntireColumn.Delete Next End Sub
VBA to Delete Entire Column in Excel : Using For Loop – Explanation
Here is the detailed explanation of the delete Column using for loop in VBA.
‘Starting the sub procedure to write VBA macro to delete Columns using VBA for loop
‘Declaring the variable iCntr to store the for loop iterations
‘looping through the Columns using for loop, here step statement indicates the iCntr increment.
For iCntr = 1 To 10 Step 1
‘deleting the first Column
‘We have delete the first Column 10 times, it means we have deleted the first 10 Columns in excel sheet using for loop and VBA.
VBA to Delete Entire Column in Excel: Using Do while Loop
Here is the example VBA macro to delete the first few Columns using Do while Loop. This will delete the first 10 records using For loop in VBA.
Sub sbVBS_To_Delete_EntireColumn_Do_while_Loop() Dim iCntr iCntr = 10 ' Last Column Do While iCntr >= 1 Columns(iCntr).EntireColumn.Delete iCntr = iCntr - 1 Loop End Sub
VBA to Delete Entire Column in Excel: Using Using Do while Loop – Explanation
Here is the explanation to the above Macro to delete Columns using Do while loop.
‘Starting the VBA macro to delete the Columns using do while loop
‘Declaring the variable store the last Column number and use it as counter variable
‘assigning the last Column value to iCntr
iCntr = 10 ‘ Last Column
‘Using do while loop we are deleting the Columns from last (10th) Column
Do While iCntr >= 1
iCntr = iCntr – 1
‘ending the sub procedure here.
Here you can observe that we are looping through the Columns from last to beginning. This will be the accurate code while deleting the Columns from worksheet. Always delete Column from last to first.
Hey Rao, thought I’d drop you a message. I’m retired after 42 years in technology at a large energy company and 5 additional years at IBM. Working now with my daughter’s company trying to get information out of QuickBooks. I used to code (back in the dark ages) Cobol, CICS, Fortran. Now I’m learning VBA and am having fun doing it after being in senior management since the mid-eighties with not much fun on a daily basis! I always thought I was a great coder. I intend to be the world’s best VBA coder (it’s all about structure). Anyway, the reason your name caught my eye… one of my old friends back in the 1990’s was a guy named Uday Rao – very smart and humorous gentleman. Drop me an email sometime.