EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

VBA Delete Column

By Dheeraj VaidyaDheeraj Vaidya

Home » VBA » Blog » VBA Lookup & Reference Functions » VBA Delete Column

VBA Delete Column

VBA Delete Column

Copy, paste, cut, delete, insert is some of the common operations used to perform in excel. The easy method to do this is, using the shortcut keys or built-in functions. But when you want to perform the operation in a single click or automatically, VBA is the solution. We can automate these tasks using excel macros. VBA also provides different methods similar to excel functions. This performs these tasks smoothly in VBA.

Columns delete method is used to delete single or multiple columns in Excel VBA. The delete property of columns is used along with column index.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax to Delete Column in Excel VBA

The syntax to delete a column in excel is as below.

Columns (Column Reference). Delete
  • Where column reference is the column number you want to delete.
  • Columns([RowIndex], ColumnIndex]) here the column range is also accepted.

Synatx of Column

How to Delete Column in Excel Using VBA?

We will learn how to Delete a Column in VBA with few examples in excel.

You can download this VBA Delete Column Excel Template here – VBA Delete Column Excel Template

VBA Delete Column – Example #1

From an attendance database, the office leaving time for some employees are given. It’s the data captured for one week, from Monday to Friday.

VBA Delete Column Example 1-1

We want to delete column Friday.

Follow the below steps to delete a column in excel.

Popular Course in this category
All in One Excel VBA Bundle (120+ Courses)120+ Online Courses | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (2,356 ratings)
Course Price

View Course

Related Courses

Step 1: So first we can create a simple function as dele() since the delete is a keyword which is not preferred.

Code:

Private Sub dele()

End Sub

VBA Delete Column Example 1-2

Step 2: Now let’s use the columns property delete.

Code:

Private Sub dele()

Columns(6).delete

End Sub

VBA Delete Column Example 1-3

Within the columns() ‘6 ‘is mentioned since the specified column is a 6th column in the table.

Step 3: Run this code by hitting F5 or Run button and see the output.

VBA Delete Column Example 1-4

If you check the table, it will look as below. Where the column Friday got deleted.

Result of Example 1-5

VBA Delete Column – Example #2

Another easy method to perform the deletion is specifying the column reference by alphabetical order.

VBA Delete Column Example 2

Follow below steps to delete a column in excel using VBA.

Step 1: Since Friday is column F, mention the column address as below in the code.

Code:

Private Sub dele1()
Columns("F").delete

End Sub

VBA Delete Column Example 2-1

While using the alphabet remember to put the alphabet in the double quotation.

Step 2: Run this code by hitting F5 or Run button and see the output.

Result of Example 2-1

This will delete column Friday from the table.

VBA Delete Column – Example #3

In the same table if you want to delete more than one column then the column range should be specified.

Follow the below steps to delete the last two columns in excel.

Step 1: The code can be modified as below.

Code:

Private Sub dele2()

Columns("E:F").delete

End Sub

VBA Delete Column Example 3-1

The range specified as “E:F” and this will delete the column from F to G.

Step 2: Run this code by hitting F5 or Run button and see the output.

Result of Example 3-2

The last two columns are deleted.

VBA Delete Column – Example #4

Let’s see what will happen if we delete the middle columns in a table.

Follow the below steps to delete the middle columns in a table.

Step 1: The code can be modified as below.

Code:

Private Sub dele3()

Columns("B:C").delete

End Sub

VBA Delete Column Example 4-1

Here “B:C” refers to the column Monday and Tuesday.

Step 2: Run this code by hitting F5 or Run button and see the output.

Result of Example 4-2

After running the code if you check the table you can see the column after “B:C” is shifted from right to left.

VBA Delete Column – Example #5

In the above example, we did not mention a worksheet name, if the workbook contains more than one sheet then the worksheet name should be specified then only the code will perform the proper action.

The two sheets contain the details of employees for two months Jan and Feb. Since the same worksheet have more than one sheet to avoid errors better to specify the sheet name.

Follow the below steps to delete a column in excel using VBA.

Step 1: The sheet should be selected using the code.

Code:

Private Sub dele4()

Worksheets("Jan").Select

End Sub

VBA Delete Column Example 5-1

Step 2: Now the code for deleting the column should be given.

Code:

Private Sub dele()

Worksheets("Jan").Select

Columns("B:C").delete

End Sub

VBA Delete Column Example 5-2

Step 3: Run this code by hitting F5 or Run button and see the output.

Result of Example 5-3

The sheet “Jan “will be selected and columns B, C i.e. Monday, Tuesday will be deleted from the table.

Deleting multiple columns using range object in VBA

The range object also used to delete a column in VBA instead of column delete. If the range object is used the code will look as below.

Private Sub dele()

Range("B:C").Delete

End Sub

VBA Delete Column 1

The Range(“B:C”) is represented the column range which needs to be deleted.

Deleting a single column using the range object

To delete a single column using the range object the range need to be specified as below.

Private Sub dele()

Range("B:B").Delete

End Sub

VBA Delete Column 2

Range (“B: B”) points to the single column and it will be deleted.

Things to Remember

  • The column can be specified using column number or corresponding alphabet while deleting.
  • When deleting multiple columns, the numbers will not be accepted as a column reference.
  • Instead of columns property, Range object can be used to delete a column in VBA.

Recommended Articles

This has been a guide to VBA Delete Column. Here we discussed how to Delete Column in Excel using VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Delete Row
  2. VBA RGB
  3. VBA Transpose
  4. VBA CDEC

All in One Excel VBA Bundle (120+ Courses)

120+ Online Courses

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
VBA Lookup & Reference Functions
  • VBA Lookup & Reference
    • VBA Selecting Range
    • VBA Range
    • VBA Match
    • VBA Transpose
    • VBA Delete Row
    • VBA Range Cells
    • VBA Delete Column
    • VBA Union
    • VBA Insert Column
    • VBA Named Range
    • VBA Hyperlink
    • VBA Last Row
    • VBA Name
    • VBA OFFSET
    • VBA Hide Columns
    • VBA Selection Range
    • VBA Columns
    • VBA Insert Row
    • VBA LOOKUP
    • VBA VLOOKUP Function
    • VBA Resize
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2020 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA Login

Forgot Password?

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

Download VBA Delete Column Excel Template

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

Special Offer - All in One Excel VBA Bundle (120+ Courses) Learn More