Updated date:

How to Show or Hide Multiple Rows in Excel Using A Button

Author:

Excel Trainer at Excel 10 Tutorial Feel free to connect to solve your excel related problems. Thanks.

Show/hide Multiple Rows in Excel

Hiding and showing rows could be crucial in Microsoft Excel if you are working on a long workbook or you are working on your custom dashboard. In this article you’ll learn how you can create a button to hide/show multiple rows in Microsoft Excel. This isn’t a built-in feature and we are going to use Microsoft Excel VBA in this tutorial.

Why you should use this?

There are tons of alternatives in excel. Sticking to any of them requires some extra features or problem-solving capabilities. In this article you will learn how you can show/hide rows using a button. Now I know you can simply select the rows and then hide it manually. But what if your workbook is long. Will you be able to select hundreds of rows manually and then hide them? Let's say you have done that. What if you need to show those hidden rows? In short you can solve almost anything in excel but solving problems fast and efficiently is something that matters.

Now you can ask several questions or even ask for several new features along with this solution. For example, you need to hide rows on different worksheets or even from different workbooks. Now that's why you have awarded with a gift named COMMENT. This article indicates a problem and solves it. If you need anything you can ask in the comment and I'll get back to you with a solution.

You can use this procedure along with any other macro and also you can create several buttons to show/hide rows using this procedure. With that said let's get started.

Step 1

First, we’re going to need a button so that we can assign some Visual Basic Coding to that button. In order to do that we’ll have to click on the Developer Tab. Developer tab is placed at the ribbon at the top of your opened workbook. If you can’t find it there then you’ll have to activate it.

To activate developer tab on your ribbon you’ll have to go to the "Customize Ribbon" option and then activate it. Go to “File”-> Click on “Option”-> Click on "Customize Ribbon”. Find the Developer Tab from the left table and then Click on “Add”. It will add the developer tab on the Ribbon.

Once you activate the developer tab you will be access it from your ribbon.

Developer Tab

how-to-show-or-hide-multiple-rows-in-excel-using-button

 Step 2

Click on “Insert” right under the “Developer Tab”.

Insert

how-to-show-or-hide-multiple-rows-in-excel-using-button

Step 3

Select the "Toggle Button". Remember to use “ActiveX Control” button.

Toggle Button

how-to-show-or-hide-multiple-rows-in-excel-using-button

Video tutorial on how to show/hide rows using a button in excel

Step 4

Draw your button. You can add stylings according to your choice. Its best you design it according to your dashboard or your workbook format. It makes it look more professional.

Design Button

how-to-show-or-hide-multiple-rows-in-excel-using-button

Step 5

After you finished designing your button “Double Click” on the button you just created. This will open the VBE or Visual Basic Editor. This Is the place where you write your VBA code.

Open VBE & Write Code

how-to-show-or-hide-multiple-rows-in-excel-using-button

Step 6

Delete Pre-occupied coding and paste this VBA code. Change the 5th line of the code according to your need. In this line the row numbers are stored. Add your row numbers here and make sure they are comma separated. Just like it is now inside the code.

VBA Code

Private Sub ToggleButton1_Click()

'Provided by Excel0Tutorial
    
    Dim xAddress As String
    Dim splitAddress As Variant
    xAddress = "1,5,8,9,10" 'change this to the row numbers
    splitAddress = Split(xAddress, ",")
    
    
    If ToggleButton1.Value Then
        For Each Var In splitAddress
            Application.ActiveSheet.Rows(Var).Hidden = True
            ToggleButton1.Caption = "Show Row"
        Next Var
    
    Else
        For Each Var In splitAddress
            Application.ActiveSheet.Rows(Var).Hidden = False
            ToggleButton1.Caption = "Hide Row"
        Next Var
    
    End If

End Sub

Step 7

Close the VBE or Visual Basic Editor. You can do that by clicking the cross on the right corner or you can Press ALT+Q on your keyboard.

Step 8

Click on the “Design Mode” button. It is placed at the top of your ribbon and under the developer tab. This will stop the designing mode. Once you are out of the designing mode your button is ready for action.

Exit Design Mode

how-to-show-or-hide-multiple-rows-in-excel-using-button

Step 9

Click on your button and it should hide the predefined rows and if you click on the button it will show those rows.

This is how you can create a simple toggle button and use a snippet of VBA code and create an awesome feature in Microsoft Excel. This will increase your productivity for sure. It will also save a lot of time. From now on you won’t have to manually find 20 rows and then hide them. Now you can just click on your custom-made button and hide those rows.

Bonus Feature

You can use this code along with other VBA codes in your workbook. Incase you need another button to show/hide another series of rows then you can use the same code alongside your previous code. In that case you’ll have to create another button and then change the button name from the code. Also change the rows you want to hide and show. Done.

Pro Tip

Save the workbook. You’ll have to save this workbook as Excel Macro Enabled Workbook (.XLSM) format otherwise your button won’t work the next time you open the workbook. If you have any question regarding this article or if you need any article relate to Microsoft excel, you can leave a comment here. I will try my best to solve your problem.

© 2019 kazi