VBA Delete multiple Columns Excel Macro Example Code

Home/VBA/VBA Delete multiple Columns Excel Macro Example Code

VBA code to Delete multiple Columns example will help us to delete multiple Columns from excel worksheet. We can use Delete method of Columns to delete the multiple Columns. In this example we will see how to delete the multiple Columns in excel worksheet using VBA. VBA code for deleting multiple 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 multiple Columns


Here is the Example VBA syntax and Example VBA Macro to delete multiple Columns from excel worksheets. This will help you to know how to delete specific and multiple Columns from Excel workbook using VBA.

VBA Delete multiple Columns: Syntax


Following is the VBA Syntax and sample VBA code to delete multiple Columns from worksheet using VBA. We are using the Delete method of the Columns object of worksheet.

Columns("
[Column Names]").EntireColumn.Delete

Here Column Names are your Column Names to delete. And EntireColumn.Delete method will delete the Entire Columns from the Excel spreadsheet.

Delete multiple Columns using VBA: Examples


The following VBA code is to delete multiple Columns from the worksheet. This code will delete the multiple Columns (A to C) which we have mentioned in the code.

Sub sbVBS_To_Delete_Multiple_Columns ()
Columns("A:C").EntireColumn.Delete
End Sub

Instructions to run the VBA code to delete multiple Columns


Please follow the below steps to execute the VBA code to delete multiple Columns from worksheets.
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 A to E
Step 6: Now press F5 to execute the code

Now you can observe that the entire Columns from A to C are deleted from worksheet.

Explained VBA Code to Delete Multiple Columns:


Starting program and sub procedure to write VBA code to delete entire Column from sheet.

Sub sbVBS_To_Delete_Multiple_Columns_C()

‘Specifying the Columns to delete and Deleting the Columns using EntireColumn.Delete method.
Columns(“A:C”).EntireColumn.Delete

End Sub
Ending the sub procedure to delete entire Column.

Here Columns(“A:C”) is to tell excel to delete Columns from A to C of the worksheet. And Delete method will delete the all specified Columns form the worksheet.

Delete Specific Multiple Columns using VBA: Examples


Here is the following VBA code is to delete specific multiple Columns from the worksheet. This code will delete the multiple specific Columns (A, C, H, K to O and Q to U) which we have mentioned in the code.

Sub sbVBS_To_Delete_Specific_Multiple_Columns()
Sheets("Sheet1").Range("A:A,C:C,H:H,K:O,Q:U").EntireColumn.Delete
End Sub

The above example delete the columns A, C, H, K to O and Q to U from the sheet named ‘Sheet1’.

LIMITED TIME OFFER
By |April 22nd, 2014|VBA|18 Comments

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).

18 Comments

  1. Shahid January 12, 2015 at 9:50 PM - Reply

    I tried this code, but it gives me an syntax error
    I do have data available from A to Z

    Can someone assist

    • PNRao January 13, 2015 at 4:15 PM - Reply

      Hi Sahid,

      This code should work, please provide the code which you are testing:

      Columns("A:Z").EntireColumn.Delete ' this will delete the Columns A to Z
      'Or if you only want to clear the data
      Columns("A:Z").Clear ' this will clear the Columns A to Z
      

      Thanks-PNRao!

  2. Samantha August 1, 2015 at 1:02 AM - Reply

    Hi, I need to delete random columns (ex. A,C,G,H). I tried doing it like you explained with the multiple random rows but it didn’t work

    Thanks!

    • PNRao August 2, 2015 at 3:38 AM - Reply

      Hi Samantha,

      You can use the below procedure to delete specific columns:

      Sub sbDeleteColumns()
      arrCol = Array("A", "C", "G", "H") 'always in ascending order
      For i = UBound(arrCol, 1) To 0 Step -1
      Columns(arrCol(i)).EntireColumn.Delete 
      Next
      End Sub
      

      Please note: Always store all the Column names in an array variable(arrCol) in ascending order.

      Thanks-PNRao!

  3. Clark August 14, 2015 at 2:55 AM - Reply

    How can I delete multiple columns for columns A, C, H, K-O, Q-U. Column K-O means column K to O. Thank you.

    • PNRao August 14, 2015 at 3:21 PM - Reply

      Hi Clark,

      I have updated the post with the example. Please check the last example to delete multiple specific columns from the excel workbook sheet.

      Thanks-Valli!

  4. chitra September 28, 2015 at 10:08 AM - Reply

    Hi Friend,

    i have data of around 900 columns and i need to delete every 3 columns after first column, how do i do this…. eg: i have to leave column 1 and delete 2,3 &4 and leave column 5 and delete 6, 7, & 8 and leave 9 and so on….. help me….

    • PNRao September 29, 2015 at 12:01 AM - Reply

      Hi Chitra,

      Please find the below code to delete columns as per above mentioned criteria.

      Sub Delete_Columns()
      Dim StCol As Integer, EnCol As Integer
      For iCntr = 1 To 900
      StCol = find_Column_Number(iCntr + 1) 'Start Column
      EnCol = find_Column_Number(iCntr + 3) 'End Column
      Sheets("Sheet1").Columns(StCol & ":" & EnCol).Delete
      Next
      End Sub
      

      ‘Function to find out column name from column number
      ‘For more detail: http://mongopono.ru/excel-vba/column-number-name/

      Function find_Column_Number(ByVal ColumnNumber As Integer)
      find_Column_Number = Replace(Replace(Cells(1, ColumnNumber).Address, “1”, “”), “$”, “”)
      End Function

      Regards- Valli

      • Syrine December 7, 2015 at 2:03 AM - Reply

        Hi,

        thank you for the code. Actually i want to execute the same code in my excel sheet because I need to do the same thing but it didn’t work. Would you help me find out what is wrong? I always receive the same error message “sub or function not defined”.

        Many thanks

        Regards

        Syrine

  5. Praveen November 1, 2015 at 11:37 PM - Reply

    Will you please help me with VBA code..
    R Name Dname Rcount Dcount
    Ramesh Arun
    Ramesh Arun
    Ramesh Ganesh 3 2(delete other duplicates keep 1st time appeared ) how to do it by vba..?

  6. Joe November 22, 2015 at 6:36 PM - Reply

    thanks for the macros — all very helpful.

    Joe

  7. Mike January 4, 2016 at 9:17 PM - Reply

    Trying to use this for deleting multiple columns:

    Sub DelCols()

    Range(“A:D,H:L,O:X,AA:BG”).EntireColumn.Delete

    End Sub

    Problem is I keep getting an exception on the colon between A & D. expected list seperator or )
    I don’t see the problem, but maybe I’ve just been staring at it too long

    Thanks

    • PNRao January 4, 2016 at 10:11 PM - Reply

      Hi Mike,
      I have tried your code and its working for me.

      'your code
      Sub DelCols()
      Range("A:D,H:L,O:X,AA:BG").EntireColumn.Delete
      End Sub
      

      Try this alternative code, this will use the Columns object:

      Sub deleteColumns()
      Dim strColumns As String
      Dim arrColumns,i
      strColumns = "A:D,H:L,O:X,AA:BG" 'Define your columns here
      arrColumns = Split(strColumns, ",")
      For i = 0 To UBound(arrColumns, 1)
      Columns(arrColumns(i)).EntireColumn.Delete
      Next i
      End Sub
      

      Thanks-PNRao!

  8. Aaron January 28, 2016 at 8:54 PM - Reply

    Hello,

    I am creating a template to be used by numerous people for various projects which, depending on the project, a different number of columns will be required.

    To help explain, Columns A&B are fixed and I do not want any to be deleted. Columns C-Q have data for them for each country that is within a project. I currently have 15 columns here as this is the max number of countries possible. If someone uses data for a project with only 3 countries, columns C, D and E would display the country name as the header and relevant data underneath, and columns F – Q will just be blank (albeit with formulae within the cell just returning a blank result). I want to use a macro so the person can just press a button and it deletes these excess columns for them.

    Is this possible? Not sure if it makes it easier but could get the header to return a certain result rather than leaving blank, e.g. in columns F-Q, using the above example, the header would be ‘DELETE’. Could I then get a macro to search for any columns that show DELETE in the header and delete the entire column?

    Thank you in advance!

  9. poorna May 14, 2016 at 10:29 PM - Reply

    HI Sir,

    I want to delete non specific columns to be delete
    i have 3 sheets, each sheets contain 100+ columns, in that i require 15 columns only
    i want 15 columns & remaining to be deleted in every sheets at a time.
    Its urgent, taking more than 15 min for 3 sheets

  10. Ramy October 11, 2016 at 4:18 PM - Reply

    Am trying to delete over 2000 columns (replacing worksheet not an option). The VBA code is this:

    wsResults.Range(“G2:BLG502”).EntireColumn.Delete

    No matter what I do (using ClearContents, Clear, repair spreadhseet, reboot, clear Windows Temp folder), I always get the message: Run-time error ‘1004’: Delete method of range failed.

    When I try to break the delete into smaller segments, such as 100 columns at a time, as in:

    wsResults.Range(“G2:DB502”).EntireColumn.Delete
    wsResults.Range(“DC10:GX502”).EntireColumn.Delete

    I get the message after roughly 700 columns.

    Am I hitting some sort of internal limitation? Is there some buffer that needs to be reset?

    Thanks.

  11. vishnu March 31, 2017 at 6:54 PM - Reply

    Hi,

    Actually i have 100 columns in my excel sheet.I have to delete all empty columns in the sheet [Here, i don’t know which column number is empty].

    please help me to resolve this issue.

    thanks!

  12. Abdul May 29, 2017 at 8:22 PM - Reply

    Hello,

    Can anyone help me,

    I want to delete multiple columns and in many sheets (delete same columns; multiple in all sheets)

Leave A Comment


Related pages


vba color indexaccess iif statement with multiple conditionsexcel check duplicate formulavba filefilterhow to declare a public variable in vbaexcel insert multiple columnshow to delete rows on excelenable macro excelhow to use sumif functiondelete duplicate data excellearn to write vba code for excelexcel range interior colorvba timer functionexcel manpower planning templatemacro loop excelvba call macroexcel xml examplehow to unhide first column in excelconcatenate excel vbavlookup advancedremove duplicate data excelload userformexcel vba open csv fileautofit in excel 2013vba copy foldervlookup example between two sheetshow do i remove blank rows in excelvba delete rowvba programming for excelhow to sort duplicates in excel 2010delete empty cells in excelexcel vba for dummies pdfkpi reporting dashboards in excelfinding duplicate cells in excelfinding duplicates in excel 2010vba activesheethow to locate duplicates in excelvb6 interview questions and answersvba database connection examplemacro code for exceloption button excel vbacopyfromrecordset vbacell in vbavba excel copy sheetfrequently asked questions in technical interviewvlookup example excelexcel rows vbavba worksheet copyvba excel macrounprotect excel 2013 workbookexcel unprotect sheet password removeractiveworkbook saveasexcel vba chrvlookup index numbermoving sheets in excelhow to find duplicate entries in excel 2007h look up in excelgroupby and orderby in sqlexcel 2010 name managerexcel to sql queryhow to create vlookup in excel 2007display duplicates in excelvba nested loopsdata mining in excel 2007how to automate graphs in excelexcel vba select casedelete columns excelbubble graphs excelvb6 rename filepivot table in excel 2007 with exampleexcel copy worksheetcombobox tutorialexcel formulas with examples pdf in hindiactivex checkboxwhat is a vlookup functionexcel count duplicates in column