Reference
This example finds all the cells in the first four columns that have a constant "X" in them and hides the column that contains the X.
Sample code provided by: Dennis Wallentin, VSTO & .NET & Excel
“`vb
Sub Hide_Columns()
‘Excel objects.
Dim m_wbBook As Workbook
Dim m_wsSheet As Worksheet
Dim m_rnCheck As Range
Dim m_rnFind As Range
Dim m_stAddress As String
‘Initialize the Excel objects.
Set m_wbBook = ThisWorkbook
Set m_wsSheet = m_wbBook.Worksheets(“Sheet1”)
‘Search the four columns for any constants.
Set m_rnCheck = m_wsSheet.Range(“A1:D1”).SpecialCells(xlCellTypeConstants)
‘Retrieve all columns that contain an X. If there is at least one, begin the DO/WHILE loop.
With m_rnCheck
Set m_rnFind = .Find(What:=”X”)
If Not m_rnFind Is Nothing Then
m_stAddress = m_rnFind.Address
‘Hide the column, and then find the next X.
Do
m_rnFind.EntireColumn.Hidden = True
Set m_rnFind = .FindNext(m_rnFind)
Loop While Not m_rnFind Is Nothing And m_rnFind.Address <> m_stAddress
End If
End With
End Sub
“`
This example finds all the cells in the first four columns that have a constant “X” in them and unhides the column that contains the X.
“`vb
Sub Unhide_Columns()
‘Excel objects.
Dim m_wbBook As Workbook
Dim m_wsSheet As Worksheet
Dim m_rnCheck As Range
Dim m_rnFind As Range
Dim m_stAddress As String
‘Initialize the Excel objects.
Set m_wbBook = ThisWorkbook
Set m_wsSheet = m_wbBook.Worksheets(“Sheet1”)
‘Search the four columns for any constants.
Set m_rnCheck = m_wsSheet.Range(“A1:D1”).SpecialCells(xlCellTypeConstants)
‘Retrieve all columns that contain X. If there is at least one, begin the DO/WHILE loop.
With m_rnCheck
Set m_rnFind = .Find(What:=”X”, LookIn:=xlFormulas)
If Not m_rnFind Is Nothing Then
m_stAddress = m_rnFind.Address
‘Unhide the column, and then find the next X.
Do
m_rnFind.EntireColumn.Hidden = False
Set m_rnFind = .FindNext(m_rnFind)
Loop While Not m_rnFind Is Nothing And m_rnFind.Address <> m_stAddress
End If
End With
End Sub
“`
About the Contributor
Dennis Wallentin is the author of VSTO & .NET & Excel, a blog that focuses on .NET Framework solutions for Excel and Excel Services. Dennis has been developing Excel solutions for over 20 years and is also the coauthor of “Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA and .NET (2nd Edition).”
!include[Support and feedback]