asked on

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,

swjtx99

Anuroopsundd

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
Columns(i).EntireColumn.Delete
End If
Next i
End Sub

swjtx99

ASKER

Thanks for the fast reply.

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

Thanks,

swjtx99

Anuroopsundd

where is the sheet attached?

swjtx99

ASKER

I'll try that again. I attached it but it didn't upload.
Delete-blank-columns.xlsm

Anuroopsundd

is your heading of the columns fixed.. i mean you will know the heading for the columns which are not empty?

swjtx99

ASKER

Yes, the column headings will always be the same name but not always in the same place.

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

swjtx99

ASKER

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()

Sheets("Gage").Select

Range("A1").Select
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

Selection.EntireColumn.Delete

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

D = D - 1
Else

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

ActiveCell.Offset(0, 1).Select
End If
Wend
Ender:

'Go back to original starting place

ActiveSheet.Range(C).Select

'End Sub

Martin Liss

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.