CODEWITHSUNDEEP
vbaexcel
ChrisSir
ChrisSir

Reputation: 3

Macro that hides columns after a filter is applied

So I have a massive table with filters based on different criteria like 'state' and 'year'. I want to be able to filter the data and then have the macro hide all the columns that are empty. My current macro only hides columns that are completely empty. I want to be able to filter by 'year' and then use the macro to hide all the empty columns, even if there is data that's hidden by the filter.

Sub HideColumns()

Dim col As Range
For Each col In Columns("Q:KN")
If Application.Count(Range(Cells(6, col.Column), Cells(168, col.Column))) = 0 Then
col.Hidden = True
End If
Next

End Sub


Sub UnhideColumns()

Columns("Q:KN").Hidden = False

End Sub

Upvotes: 0

Views: 526

Answers (1)

Shawn V. Wilson
Shawn V. Wilson

Reputation: 1111

The .SpecialCells(xlCellTypeVisible) property will "make" a range composed of the non-hidden cells. Try this:

If Application.Count(Range(Cells(6, col.Column), Cells(168, col.Column)).SpecialCells(xlCellTypeVisible)) = 0 then

Upvotes: 1

Related Questions