How to Hide Columns with a Button in Excel – 4 Methods

This is the sample dataset.

hide columns in excel with button

 


Method 1 – Using the Column Property to Hide Columns with Button in Excel

1.1. Hiding a Single Column

To hide column D.

Steps:

  • Go to the Developer Tab and select Visual Basic.

  • In the VBA editor, select Insert >> Module to open a VBA Module.

hide columns in excel with button

  • 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).

hide columns in excel with button

  • Select the Macro.
  • Click OK.

  • Name your button in Edit Text.

hide columns in excel with button

Your button is set.

  • Click the button and column D will hide.

hide columns in excel with button

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.

hide columns in excel with button

 

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.

hide columns in excel with button

 

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 and will hide.

hide columns in excel with button

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.

hide columns in excel with button

The code will hide column E.

  • To hide column F, click the button again.

hide columns in excel with button

 

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).

hide columns in excel with button

  • 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.

hide columns in excel with button

  • If you click the button again, the E and F columns will unhide.

 


Download Practice Workbook

 


Related Articles


<< Go Back to Hide Columns | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo