Apr 18 2022 01:40 AM
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
Apr 18 2022 03:52 AM
SolutionI 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
Apr 18 2022 06:30 AM
Apr 18 2022 08:39 AM
Apr 18 2022 08:39 AM
Apr 18 2022 03:52 AM
SolutionI 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