MrExcel Publishing
Your One Stop for Excel Tips & Solutions

delete blank columns macro needed


Posted by Steve on December 21, 2001 9:35 AM

Hi there,

Can someone help with a macro or suggestion on how to automate deleting blank columns in an excel sheet. The blank columns may not always be in the same place...

Thanks tons!


Posted by Damon Ostrander on December 21, 2001 12:21 PM

Hi Steve,

Here is such a macro:

Sub DelEmptyCols()
' Deletes all empty columns on the active worksheet
Dim iCol As Integer
With ActiveSheet.UsedRange
For iCol = .Column + .Columns.Count - 1 To 1 Step -1
If IsEmpty(Cells(65536, iCol)) And IsEmpty(Cells(1, iCol)) Then
If Cells(65536, iCol).End(xlUp).Row = 1 Then Columns(iCol).Delete
End If
Next iCol
End With
End Sub


Happy computing.

Damon