How to unhide and unhide certain column based on cell value

New Contributor

Currently I'm working on Excel sheets whereas I need certain columns to be hidden based on cell value.

for example if B2=1, then i want Column 5 to 8 similarly if B2=2, then i want column 9 to 12, hence in the same way if B2=3, then i want column 13 to 15. and all other should be hidden

4 Replies
best response confirmed by White_fang_of_leaf (New Contributor)


I assume that columns A to D, or at least column B, should remain visible (if you hide column B, you cannot change B2).

Right-click the sheet tab.

Select View Code from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B2"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Range("E1:XFD1").EntireColumn.Hidden = True
        Select Case Range("B2").Value
            Case 1
                Range("E1:H1").EntireColumn.Hidden = False
            Case 2
                Range("I1:L1").EntireColumn.Hidden = False
            Case 3
                Range("M1:O1").EntireColumn.Hidden = False
        End Select
        Application.ScreenUpdating = True
    End If
End Sub
Hans has given you good answer using VBA (macros). Alternatively I would ask why you want to 'hide'/'show' those columns. Excel also offer the ability to create 'links' that can 'jump' the screen to those locations. Depending on your application using these links and creating a 'dashboard' might be a more effective solution. I recommend you look up some guides on Excel Dashboards if you are not familiar with them.
thank you for the information
Thank you and hope it works.