This is the sample dataset.
Method 1 – Using the Column Property to Hide Columns with Button in Excel
1.1. Hiding a Single Column
To hide column D.
- Go to the Developer Tab and select Visual Basic.
- In the VBA editor, select Insert >> Module to open a VBA Module.
- Enter the following code in the VBA Module.
Sub HideByColumnProperty()
Columns("D").Hidden = True
End Sub
This code will hide Column D after running the Macro.
- Go back to your sheet and select Developer >> Insert >> Button (Form Controls).
- Select the Macro.
- Click OK.
- Name your button in Edit Text.
Your button is set.
- Click the button and column D will hide.
You can also create a button by selecting Insert Tab >> Illustrations >> Shape >> and assign the Macro in it in the Context Menu (right-click the shape).
Read More: Hide Columns with No Data in Excel
1.2. Hiding Multiple Columns with Button in Excel
To hide columns E and F.
Steps:
- Open a Module and enter the following code.
Sub HideMultipleByColumnProperty()
Columns("E:F").Hidden = True
End Sub
This code will hide columns E and F after running the Macro.
- Assign this macro to a button and click it.
Read More: How to Hide Columns Without Right Click in Excel
Method 2 – Using the Range Property To Hide Columns with a Button in Excel
2.1. Hiding a Single Column
To hide column D.
Steps:
- Follow the steps in Method 1 to open a VBA Module.
- Enter the following code.
Sub HideByRangeProperty()
Range("D:D").EntireColumn.Hidden = True
End Sub
- Assign this Macro to a new button, as described in Method 1.
- Click the button. Column D will hide.
Read More: How to Hide and Unhide Columns in Excel
2.2. Hiding Multiple Columns with a Button in Excel
To hide columns E and F:
Steps:
- Enter the following code in a new module.
Sub HideMultipleByRangeProperty()
Range("E:F").EntireColumn.Hidden = True
End Sub
- Assign this macro to a button and click it. Columns E and F will hide.
Read More: How to Hide Extra Columns in Excel
Method 3 – Hiding Columns One after the other with a Button in Excel
Steps:
- Follow the steps of Method 1 to open a VBA Module.
- Use the following code in the VBA Module.
Sub HideColumnsBySelectionAndRepeatedly()
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Selection.EntireColumn.Hidden = True
End Sub
This code will hide the column next to your selected column or a cell.
- Assign this Macro to a new button, as described in Method 1.
- Select any cell in the column before the column you want to hide: columns E and F. Here, a cell in column D.
- Click the button.
- Use a Text Box in the sheet header. This code will hide all the columns in your dataset if you merged cells in the header.
The code will hide column E.
- To hide column F, click the button again.
Read More: How to Unhide Columns in Excel
Method 4 – Hiding Columns with a Toggle Button in Excel
Steps:
- Follow the steps in Method 1 to open the VBA Editor.
- Open the toggle sheet in the VBAProject and enter the following code in it.
Private Sub ToggleButton1_Click()
Dim mnColumns As String
mnColumns = "E:F"
If ToggleButton1.Value Then
Application.ActiveSheet.Columns(mnColumns).Hidden = True
Else
Application.ActiveSheet.Columns(mnColumns).Hidden = False
End If
End Sub
This code will hide columns E and F when you press the Toggle button. It will also unhide the columns if you click the button again. It uses an IF Statement to activate and deactivate the Columns.Hidden property.
- Save the Excel workbook by pressing CTRL+S and create a Toggle.
- Go to Developer >> Insert >> Toggle Button (Active X Form).
- Turn off the Design Mode by clicking it. There is a Toggle Button in the sheet.
- Click the Toggle This will hide columns E and F.
- If you click the button again, the E and F columns will unhide.
Download Practice Workbook
Related Articles
- How to Unhide Columns in Excel All at Once
- Unhide Columns Is Not Working in Excel
- Unhide Columns in Excel Shortcut Not Working
<< Go Back to Hide Columns | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!