Question
Hi, I am looking for a solution to hide columns based upon their content.
I know this is logically possible but I am struggling with the "How" and really do hope you can help me.
Background Info: I have a database which holds data from which I am building reports for users. This database has an Excel Add-In which allows me to create reports (views) in Excel to distribute to users. The Add-In has a switch which automatically suppresses any rows which has zero's in the cells across the columns.
Problem: The report I am building uses 219 columns. Most of these columns contain zero's. I would like to build a macro to hide these columns so that the users only see those columns with data in them.
Thank you in advance for your help.
Kind regards,
Reshma
Answer
Hi Reshma,
Here is a macro that will hide zero-filled or empty columns:
Sub HideEmptyColumns()
' This macro hides empty or all-zero columns in the active worksheet
' It ignores columns outside the used range (UsedRange)
Dim Col As Range
For Each Col In ActiveSheet.UsedRange.Columns
Col.EntireColumn.Hidden = AllZero(Col)
Next Col
End Sub
Sub UnhideColumns()
' This macro unhides all columns on the active worksheet
Columns.Hidden = False
End Sub
Function AllZero(R As Range) As Boolean
' This function returns TRUE if the range is entirely empty or all zero
Dim C As Range
AllZero = True
For Each C In R.Cells
If C.Value <> 0 Then
AllZero = False
Exit For
End If
Next C
End Function
I also included an UnhideColumns macro to enable you to easily unhide the hidden columns. The user-defined function AllZero is a "helper" function used by the HideEmptyColumns macro.
Simply install this code in a standard macro module just as you would any other macro. The macro will always operate on the active worksheet.
Keep Excelling.
Damon
Quote