I'm using the following to hide blank columns when column A is autofiltered to a specific selection, unhides/rehides when the selection changes. Works great in excel 2003, but is freezing up the workbook in excel 2010. Any help is appreciated!
Private Sub Worksheet_Calculate()
Dim rngFlterRange As Range
Dim rngCol As Range
If Me.AutoFilterMode Then
If Me.AutoFilter.Filters(1).On Then
Set rngFlterRange = Me.AutoFilter.Range
Set rngFlterRange = rngFlterRange.Offset(1).Resize(rngFlterRange.Rows.Count - 1)
Application.ScreenUpdating = False
rngFlterRange.EntireColumn.Hidden = False
For Each rngCol In rngFlterRange.Columns
If Application.CountA(rngCol.SpecialCells(xlCellTypeVisible)) <> 0 Then
If rngCol.EntireColumn.Hidden Then
rngCol.EntireColumn.Hidden = False
End If
Else
If rngCol.EntireColumn.Hidden = False Then
rngCol.EntireColumn.Hidden = True
End If
End If
Next rngCol
Else
Me.Columns.Hidden = False
End If
Else
Me.Columns.Hidden = False
End If
End Sub
Bookmarks