Automatically hide empty columns from Filtered Table based on Totals Row

[XLn3wb]

Hello,

I am very new to VBA so I can't work out a way to do the following:

I have an Excel Table with multiple Rows and Columns. What I would like to be able to do is, after applying a Filter on the table, all columns which have only 0 or empty cells to become automatically hidden.

I thought that perhaps an easy solution would be to first to use the Count function on the Totals Row of the Table. Therefore, if after applying a filter, all the cells were 0 or empty the Totals Row would be equal to zero too since this function is dynamic and changes after applying a filter.

VBA could check the value on the Totals Row of the table using ActiveSheet.ListObjects("Table1").TotalsRowRange.Select and if the cell is equal to 0 after applying the filter then it would hide the respective column using the function .EntireRow.Hidden = True .

Unfortunately, I don't know how to write this down as a VBA module. I 'd appreciate any help.

Hide Empty Columns.xlsx

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[skywriter]

I should have refreshed before posting.
John has provided a solution.

[XLn3wb]

Thank you for your post John. It works and only needs a tweak to make the columns unhide once you change the filter because otherwise you will end up with less columns showing.

A colleague just offered also this solution which works perfectly.

Please Login or Register  to view this content.

[JOHN H. DAVIS]

You're welcome. Glad you found a working solution.