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.
We want to delete the blank columns from the data, follow below given steps and 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.
To run the code press the key F5 on your keyboard,
In this way, we can delete the empty columns through VBA in Microsoft Excel.
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
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
Here is how to use it to remove if a header is populated but the column is blank below it.
Sub Delete_Columns()
Dim C As Integer
C = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
Do Until C = 0
If WorksheetFunction.CountA(Columns(C)) = 1 Or WorksheetFunction.CountA(Columns(C)) = 0 Then
Columns(C).Delete
End If
C = C - 1
Loop
End Sub
Works great for rows too:
Sub Delete_Rows()
Dim R As Integer
R = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
Do Until R = 0
If WorksheetFunction.CountA(Rows(R)) = 0 Then
Rows(R).Delete
End If
R = R - 1
Loop
End Sub
How about a version of this where it deletes all blank columns even if there is header text in the header row (and, obviously, would delete that column's header text cell as well...)?
Hello-How can you run this exact macro but take into consideration that there may be a column heading? For instance, let's say there is a column heading for Phone number, but no phone numbers are provided-can we run a code to delete empty columns AFTER skipping over the column heading?
Thanks!
your code is not working