PDA

View Full Version : Delete empty column in mutliple excel worksheets



pancake
06-14-2007, 11:10 PM
I have saw the post of delete empty columns for excel file. I tried to use the code. but nothing happen.

The file i have is 3 worksheets with 100 rows on each. Each worksheet has 15-20 columns. The empty columns are varied in each worksheet.

Could anyone help?

This will really save me a lot of time... otherwise i have to do this manually...

Thanks in advance

:dunno

mikerickson
06-14-2007, 11:28 PM
This routine assumes that you have an entry in row1 on the rightmost column.
It should do what you want.
Sub test()
Dim aCol As Range, oneCol As Range
Dim uR As Range, xSheet as Worksheet
Dim i As Long
For Each xSheet in ThisWorkbook.Worksheets
With xSheet
Set uR = Range(.Range("a1"), .UsedRange)

For i = .Cells(1, .Columns.Count).End(xlToLeft).Column To 1 Step -1
Set aCol = Application.Intersect(uR, .Cells(1, i).EntireColumn)
On Error Resume Next
Set oneCol = aCol.SpecialCells(xlCellTypeBlanks)
If Err = 0 Then
If oneCol.Address = aCol.Address Then
aCol.EntireColumn.Delete shift:=xlLeft
End If
End If
On Error GoTo 0
Next i
End With
Next xSheet
End Sub