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


word 2007 add developer tab to ribbonvba event handlerexcel insert chart titledashboard templates excelmerge multiple excel sheets into onerelease plan excel templatefind duplicates in excel 2010select column vbauseful excel macros examplessql written interview questionspowerpoint donut chartvba delete worksheetunprotect excel file 2010excel macro tutorial pdfmerge excel rowsvba combobox codelookup vba excelopen userform vbaexcel vba inputbox cancelhow to merge multiple columns in excelif clause in excelvbscript read excelvlookup easy examplehyperlink macroautofilter field vbaexcel macro active cellvlookup functions in excel 2010using offset function in excelwriting functions in vbaaccess vba do while loopvbscript coding examplesvba worksheet deleteexcel formula colorshortcut key for replace in excelhow to change cell width in excelstacked clustered chart excel 2013macro to insert rows in excelinsert button in excel 2007excel concatinationhow to use a pivot table in excel 2010vba combobox rowsourceundo vbaplanning xls templateshow to unhide rows and columns in excel 2010excel sort vbaexcel vba macrocheckbox excel vbavba access sqlvba excel to powerpointxls vlookupif statement in ms excelvba loop statementexcel macro open workbookexcel ifsmacro function in excelbase sas tutorial for beginnersproject plan template microsoft wordproject charter template powerpointbasic excel macroado vba excelhow to learn excel macro programmingmicrosoft excel test questions and answersexcel macros examplesvba until looppivot table tutorial excel 2010excel task tracker templateexcel vba copy pastesql faqs with answersuserform controlsvba formulasexcel 2010 remove blank rowsunhide personal workbookexcel deduplicatevba application function