Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

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!


Check out our Excel VBA Resources

Re: delete blank columns macro needed

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.