Deleting Empty Columns from all Worksheets

I have a macro which deletes empty columns from a worksheet even if that particular column has a header. It works fine, however I need it to loop through all of the worksheets in the workbook. The worksheet names can change so I can't specifically reference worksheet names in the code. Sounds simple enough but I have tried various methods and nothing seems to work. The following code will delete the empty columns on the active worksheet but then I get an error: run-time error '424' object required. Any suggestions would be greatly appreciated.


Sub DelColumns()
Dim ws As Worksheet, r As Range, c As Range, del As Range

For Each ws In ActiveWorkbook.Worksheets

Set ws = ActiveSheet
Set r = Intersect(ws.Rows(1), ws.UsedRange)
For Each c In r
If WorksheetFunction.CountIf(c.EntireColumn, ">""""") = 1 Then ' or If IsEmpty(c.End(xlDown)) Then
If del Is Nothing Then
Set del = c
Else
Set del = Union(del, c)
End If
End If
Next
If Not del Is Nothing Then del.EntireColumn.Delete


Next ws

End Sub

Welcome to the forum.

You should put your code within code tags (as per forum rules).

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Not Quote Tags