How to Delete Empty Columns through VBA in Microsoft Excel?

 

In this article, we will learn about how to delete empty columns through VBA in Microsoft Excel. We will delete the columns which are completely empty, it means if there is no data within the entire column that data.

 
Let’s take an example and understand:

We have data in range A1:I21, in which column A contains Name, column B is blank, column C contains Street address, column D City, column E is blank, column F contains region, column G is blank, column H contains country name, and column I contains phone number.
image 1

 

We want to delete the blank columns from the data, follow below given steps and code:

  • Open the VBE page to press the key Alt+F11
  • Insert the module to write the code
  • Write the below mentioned code:

 

Sub Delete_Columns()

Dim C As Integer

C = ActiveSheet.Cells.SpecialCells(xlLastCell).Column

Do Until C = 0

If WorksheetFunction.CountA(Columns(C)) = 0 Then

Columns(C).Delete

End If

C = C - 1

Loop

End Sub

 

Code Explanation:

First we have defined all the variables. Then the range of blank columns, then we have used “Do until loop” to delete the blank columns. And, now we have defined the blank column and delete command.

image 2

 

To run the code press the key F5 on your keyboard,

  • All blank columns will get delete.

image 3

 

In this way, we can delete the empty columns through VBA in Microsoft Excel.

 

image 48
 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 
 



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>