EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home VBA VBA Resources VBA Lookup & Reference Functions VBA Delete Column

VBA Delete Column

Madhuri Thakur
Article byMadhuri Thakur

Updated May 26, 2023

VBA Delete Column

VBA Delete Column

ome common operations performed in Excel include copy, paste, cut, delete, and insert. The easy method to do this is, using the shortcut keys or built-in functions. But VBA is the solution to serve the operation in a single click or automatically. We can automate these tasks using Excel macros. VBA also provides different methods similar to Excel functions. This performs these tasks smoothly in VBA.

Watch our Demo Courses and Videos

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

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

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 Columns in Excel Using VBA?

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

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

VBA Delete Column – Example #1

The attendance database provides office leaving time for some employees. 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.

Step 1: First, we can create a simple function as dele() since the delete is a keyword that 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 the 6th column in the table.

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

VBA Delete Column Example 1-4

If you check the table below, you will see that the column “Friday” is 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 the below steps to delete a column in Excel using VBA.

Step 1: Since Friday is column F, mention the column address 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 the 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

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

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 is specified as “E:F,” which will delete the column from F to G.

Step 2: Run this code by hitting the 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 the 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: You should select the sheet using the code.

Code:

Private Sub dele4()

Worksheets("Jan").Select

End Sub

VBA Delete Column Example 5-1

Step 2: 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 the F5 or Run button and see the output.

Result of Example 5-3

I will select the ‘Jan’ sheet and delete columns B and C, which represent Monday and Tuesday, from the table.

Deleting multiple columns using range object in VBA

The range object also deletes a column in VBA instead of column delete. If you use the range object, the code will look as follows:

Private Sub dele()

Range("B:C").Delete

End Sub

VBA Delete Column 1

The range (“B:C”) represents the column range that needs to be deleted.

Deleting a single column using the range object

To delete a single column using the range object, the range must be specified 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 the column number or corresponding alphabet while deleting.
  • You cannot use numbers as column references when deleting multiple columns.
  • Instead of the columns property, the Range object can delete a column in VBA.

Recommended Articles

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

  1. VBA Delete Row
  2. VBA RGB
  3. Excel Spell Check
  4. VBA CDEC
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
  • Blog as Guest
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

EDUCBA

Download VBA Delete Column Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

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

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

Forgot Password?

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Cyber Monday Reloaded Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW