Delete Blank Columns

[billandrew]

Evening All

Is there a possible shorter way to delete Columns with No Data.

Thanks

Sub DeleteBlankCol()
    col = Cells(1, Columns.Count).End(xlToLeft).Column
    For c = col To 1 Step -1
    If WorksheetFunction.CountA(Columns(c)) = 0 Then
    Columns(c).Delete
    End If
    Next
    End Sub

 

[MARK858]

I don't know about shorter (or why the code length would be an issue ) but the below is probably faster (untested).
Assumes there is data in column A.

[color=darkblue]Sub[/color] RemoveBlankColumns()
    [color=darkblue]Dim[/color] myRng [color=darkblue]As[/color] Range, RngDel [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] ColCount [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] x [color=darkblue]As[/color] [color=darkblue]Long[/color]

    Application.ScreenUpdating = [color=darkblue]False[/color]

    [color=darkblue]Set[/color] myRng = ActiveSheet.UsedRange
    ColCount = myRng.Columns.Count

    [color=darkblue]For[/color] x = ColCount [color=darkblue]To[/color] 1 [color=darkblue]Step[/color] -1
        [color=darkblue]If[/color] Application.WorksheetFunction.CountA(myRng.Columns(x)) = 0 [color=darkblue]Then[/color]
            [color=darkblue]If[/color] RngDel [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] [color=darkblue]Set[/color] RngDel = myRng.Columns(x)
            [color=darkblue]Set[/color] RngDel = Union(RngDel, myRng.Columns(x))
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] x

    [color=darkblue]If[/color] [color=darkblue]Not[/color] RngDel [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        RngDel.EntireColumn.Delete
    [color=darkblue]End[/color] [color=darkblue]If[/color]

    Application.ScreenUpdating = [color=darkblue]True[/color]

[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Rick Rothstein]

Evening All

Is there a possible shorter way to delete Columns with No Data.

Thanks

Sub DeleteBlankCol()
    col = Cells(1, Columns.Count).End(xlToLeft).Column
    For c = col To 1 Step -1
    If WorksheetFunction.CountA(Columns(c)) = 0 Then
    Columns(c).Delete
    End If
    Next
    End Sub

Well, here is one way you could "shorten" it

Sub DeleteBlankCol()
   For c = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
     If WorksheetFunction.CountA(Columns(c)) = 0 Then Columns(c).Delete
   Next
 End Sub

 

[billandrew]

Thank You both - much appreciated.