Delete blank columns

Need to delete all blank columns in a sheet..

I get a sheet routinely that has no more than 20 columns of data but there are often blank columns between populated columns and it's not always the same column that is blank. I'm already running a macro to change fonts, colors, etc. so I am looking for some code to add to the VB macro that would also delete those pesky blank columns.

Thanks in advance,

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sub DeleteEmptyCols()
    Dim LastCol As Long, i As Long
    LastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Columns
    For i = LastCol To 1 Step -1
        If WorksheetFunction.CountA(Columns(i)) = 0 Then
        End If
    Next i
End Sub
swjtx99Author Commented:
Thanks for the fast reply.

There is an error. Attached example sheet with above macro inserted.


where is the sheet attached?
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

swjtx99Author Commented:
I'll try that again. I attached it but it didn't upload.
is your heading of the columns fixed.. i mean you will know the heading for the columns which are not empty?
swjtx99Author Commented:
Yes, the column headings will always be the same name but not always in the same place.
Martin LissOlder than dirtCommented:




Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
swjtx99Author Commented:
Thanks. Works great. Also found this after some late night googling. Yours is less lines so not sure what the difference is but they both work.

Sub Step_21_DeltBlnkCol()


C = ActiveCell.Address
D = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

'Keep going through all the columns that contain data

While ActiveCell.Column <= D

' Check to see if the column is empty

  If WorksheetFunction.CountA(Selection.EntireColumn) = 0 Then

'   If it is empty delete it


'   Decrement the number of columns that contain data as one was just deleted

    D = D - 1

'   If is is not empty go to the next column to test it

    ActiveCell.Offset(0, 1).Select
  End If

'Go back to original starting place


'End Sub
Martin LissOlder than dirtCommented:
You're welcome but I think you should 'Request Attention' so that you can give Anuroopsundd at least half the points since all I did was to correct one line in his solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.