How to Hide Columns with Button in Excel (4 Suitable Methods)

The article will show you how to hide columns in Excel with a button. Typically, it’s a common thing to hide unnecessary columns while working on the data of an Excel sheet. And this can be easily done by the command from the context menu or Format ribbon. But when your dataset contains an excessive amount of columns, you may find it tiresome to hide these columns one by one by command. However, you can use Macros to hide columns as many as you wish just by setting the range in the code. In this article, we’ll show you how to assign these types of Macros in a button so that you can operate the hide columns command with just one click on the mouse.

In the dataset, we have some information about some people, their names, salaries, savings, etc.

We will show you how to hide some of the columns of the dataset in the later sections.

1. Using Column Property to Hide Columns with Button in Excel

Whenever we use a button to execute a command in an Excel sheet, we need to use Microsoft Visual Basic for Applications (VBA) and assign the Macro to that button. Here, we will use the VBA Column property to hide columns with the help of a button. We can do this operation for both single and multiple columns. Let’s discuss this matter in the following section.

1.1. Hiding Single Column

We will use the Columns.Hidden property to hide column D. Let’s follow the instructions below.

Steps:

• First, go to the Developer Tab and then select Visual Basic.

• After that, the VBA editor will appear. Select Insert >> Module to open a VBA Module.

• Now, type the following code in the VBA Module.

Sub HideByColumnProperty()
Columns("D").Hidden = True
End Sub

This code will hide Column D after running the corresponding Macro.

• After that, go back to your sheet and select Developer >> Insert >> Button (Form Controls).

• After that, assign your desired Macro.
• Click OK.

• Next, give your button a name by selecting Edit Text.

• Now your button is all set.

• Just click on the button and you will see that column D is now invisible.

Thus you can hide a single column in Excel with a button using the Column property. You can also make a button by selecting Insert Tab >> Illustrations >> Shape >> Any shape of your choice and assign the Macro in it from the Context Menu (right-click on the shape).

Read More: Hide Columns with No Data in Excel

1.2. Hiding Multiple Columns with Button in Excel

Say, we want to hide columns E and F. Let’s go through the procedure below. We recommend you read Section 1.1. completely because of some steps we are going to skip here.

Steps:

• After opening a Module type the following code in a new module.

Sub HideMultipleByColumnProperty()
Columns("E:F").Hidden = True
End Sub

This code will hide the columns E and F after running the Macro.

• After that, assign this macro to a button and click on it.

Thus you can hide multiple columns using the Column property.

Read More: How to Hide Columns Without Right Click in Excel

2. Using Range Property To Hide Columns with Button in Excel 

We can also use the VBA Range property to hide columns with the help of a button. We can do this operation for both single and multiple columns. Let’s discuss this matter in the following section.

2.1. Hiding Single Column

We will use the Range.Hidden property to hide column D. Let’s follow the instructions below.

Steps:

• First, follow the steps of Section 1 to open a VBA Module.
• Next, type the following code in the VBA Module.

Sub HideByRangeProperty()
Range("D:D").EntireColumn.Hidden = True
End Sub

• After that, assign this Macro to a new button as we did in Section 1.
• Thereafter, click on that button. You will see the column D disappears.

Thus you can hide a single column in Excel with a button using the Range property.

Read More: How to Hide and Unhide Columns in Excel

2.2. Hiding Multiple Columns with Button in Excel

Say, we want to hide columns E and F. Let’s go through the procedure below.

Steps:

• Type the following code in a new module.

Sub HideMultipleByRangeProperty()
Range("E:F").EntireColumn.Hidden = True
End Sub

• After that, assign this macro to a button and click on it. This operation will hide the E and F columns

Thus you can hide multiple columns using the Range property.

Read More: How to Hide Extra Columns in Excel

3. Hiding Columns One after Another with Button in Excel 

You can make a button to use it for hiding columns one after another. Let’s get to the business of this topic.

Steps:

• First, follow the steps of Section 1 to open a VBA Module.
• Next, type 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.

• After that, assign this Macro to a new button as we did in Section 1.
• Thereafter, select any cell of the column that is previous to the column you want to hide. In this case, I want to hide columns E and F. So I selected a cell of column D and then clicked on the button.
• There’s one more thing that I did here. I used a Text Box for the sheet header to make the code run perfectly. This code will hide all the columns of your dataset if you use a merged cell for the header of that dataset.

• This operation will hide column E.

• If you want to hide column F, then click on the button again.

Thus you can hide a single column in Excel with a button repeatedly by the Selection property. You can hide more columns if you just click the button continuously.

Read More: How to Unhide Columns in Excel

4. Hiding Columns with Toggle Button in Excel

If you want to hide your data for a brief moment and after that period, you want that data back from the column, you should use a toggle button and assign a Macro in it. Please follow the instructions below.

Steps:

• First, follow the steps of Section 1 to open the VBA Editor.
• Next, open the toggle sheet from the VBAProject and type 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 the columns E and F when you press the Toggle button first and also bring back the columns if you click the button again. It uses an IF Statement to activate and deactivate the Columns.Hidden property to toggle the hide and unhide operations respectively.

• After that, save the Excel workbook by pressing CTRL+S and create a Toggle To get this button, go to Developer >> Insert >> Toggle Button (Active X Form).

• Thereafter, you need to turn off the Design Mode just by clicking on it. You can also notice that there is a Toggle Button on the sheet.

• Now, click on the Toggle This will hide the columns E and F.

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

Thus you can hide multiple columns in Excel at once with a Toggle button and at the same time, you can bring them back too. It’s a time-saving process because unhiding the columns one by one or by commands is not going to be an efficient method.

Download Practice Workbook

Conclusion

In the end, we can conclude that you will learn some easy and effective tricks to hide columns in Excel with a button after reading this article. If you have any better methods questions or feedback regarding this article, please share them in the comment box.

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 | Columns in Excel | Learn Excel