Forum Discussion
How to unhide and unhide certain column based on cell value
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
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
4 Replies
- 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
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- Thank you and hope it works.
