VBA Delete Entire Column Excel Macro Example Code

Home/VBA/VBA Delete Entire Column Excel Macro Example Code

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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.


Columns(

[Column Number]).EntireColumn.Delete

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
Sub sbVBS_To_Delete_EntireColumn_C()

‘Specifying the Columns to delete and Deleting the Columns using EntireColumn.Delete method.
Columns(1).EntireColumn.Delete

‘Ending the sub procedure to delete entire Column
End Sub

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
Sub sbVBS_To_Delete_FirstFewColumns_in_Excel_C()

‘First Column is deleting
Columns(1).EntireColumn.Delete
‘Now Second Column becomes the first Column- again we are deleting the first Column
Columns(1).EntireColumn.Delete
‘Now Third Column becomes the first Column- again delete command is deleting the first Column
Columns(1).EntireColumn.Delete
‘We have deleted the first 3 Columns, and ending the sub procedure here.
End Sub

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
Sub sbVBS_To_Delete_EntireColumn_For_Loop_C()
‘Declaring the variable iCntr to store the for loop iterations
Dim iCntr
‘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
Columns(1).EntireColumn.Delete
Next
‘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.
End Sub

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
Sub sbVBS_To_Delete_EntireColumn_Do_while_Loop()
‘Declaring the variable store the last Column number and use it as counter variable
Dim iCntr
‘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
Columns(iCntr).EntireColumn.Delete
iCntr = iCntr – 1
Loop
‘ending the sub procedure here.
End Sub

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.

LIMITED TIME OFFER
By |April 22nd, 2014|VBA|1 Comment

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

One Comment

  1. Leo Haas April 17, 2015 at 11:46 PM - Reply

    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.

Leave A Comment


Related pages


excel deduphow to add command button in excel 2007corporate minutes template wordexcel hlookup tutorial pdfinterview questions & answers for fresherspaste values vbaexcel vba powerpointexcel 2007 hide columnsif isnumeric vbahow to check duplicate records in excelddl and dml commands in sqlcode vbaexcel 2010 iferrorvba excel dimcombobox templatedo while loop in vbahide a column in excelsum function in vbavba open xls fileexcel vba array lengthexcel vba delete rowexcel vba loopdefinition of filtering in excelexcel macro change cell colordashboard excel tutorialrgb excel vbause vlookup in vbaforgotten password in excel to unprotect sheetadvanced excel vba tutorialisblank excel functionconstruction cost template excelvba for excel pdfvba excel count rowsexcel 2003 vba tutorialdml statements in sqlexcel vba usesmacros excel tutorialform vba excelunlock worksheetexcel macro fileexcel find and remove duplicatesmicrosoft excel 2007 exam questions and answersexcel macro column widthexcel checkboxessample excel macro codevba byrefvariables vbaforgot password for excel workbookvba code to open excel fileformula for vlookup in excel 2007code vba excelundo macro excelexcel construction estimating templateunhide personal workbooksumif function excel 2007sas beginnersconcatenate excel exampleexcel 2010 vlookup functionformulas in vbavlookup definationfunction excel vbauserform loadexcel lookup vectorhow to sort alphabetically in exceloffset function in vbaduplicate function in excelrun a macro in excel 2007sql query practice questions answers pdfjob estimate template wordhow to get rid of duplicate rows in excelvba case sensitivevba code to insert data into access table