How To Unhide Multiple Sheets In Excel At Once With VBA, Not One By One

excel.jumanto.com - How To Unhide Multiple Sheets In Excel At Once With VBA. In the previous article, we've learned how to hide and unhide an excel sheet or multiple sheets, with right-click, by menu in the ribbon and shortcut Alt + F11.

Unhiding all worksheets within an excel workbook can not be done by right click and then choose unhide.

If we do that, it just unhides an excel sheet, not all the worksheets that have been hidden before.

So, in this article, I want to share, how do I unhide all sheets at once, don't unhide them one by one that will take us more time to do that.

Let's practice how do I unhide all sheets excel VBA below. Like I've written before, the best way to learn excel is by more practice in our daily work.

Best Way To Unhide Multiple Sheets At Once By VBA

Ok, for the clear lesson now, I'll give an example excel sheet.

the sheets that I'll hide

I've 4 sheets in my excel workbook, then I rename my worksheets below:

• sheet1: January
• sheet2: february
• sheet3: march
• sheet4: I don't rename it

And then I hide 3 worksheets: january, february, march, at once by click on january, hold CTRL keyboard, and then click februari, march, and then right-click and I Choose hide option.

After that, sheet january, february, march are hidden.

I can't see them anymore in my excel workbook.

After I hide my worksheets, three worksheets, then I'll view it again at once, not one by one.

Here are the ways that I do, step by step:

1. Press Alt + F11 to open Visual Basic 

On my keyboard, press Alt + F11 to open microsoft visual basic (VBA). In my excel, appears window like this picture.

insert module to view all unhide excel worksheets

2. Then, right click on VBAProject > Insert > Module.

In the module window, copy this code and then paste to the module:

Sub Unhide_All_Sheets_Count()
Dim wks As Worksheet
Dim count As Integer

count = 0

For Each wks In ActiveWorkbook.Worksheets
If wks.Visible <> xlSheetVisible Then
wks.Visible = xlSheetVisible
count = count + 1
End If
Next wks

If count > 0 Then
MsgBox count & " worksheets have been unhidden.", vbOKOnly, "Unhiding worksheets"
Else
MsgBox "No hidden worksheets have been found.", vbOKOnly, "Unhiding worksheets"
End If
End Sub

VBA code to view all hidden sheets at once

3. Press F5 to view all worksheets that have been hidden

After I press F5 on my keyboard, all sheets that have been hidden, now viewable again and on the screen appears a notification like this.

hidden sheets are viewable again

You can see on the left bottom, sheet january, february and march is viewable again.

I use excel 2007 to practice this method, to unhide all hidden worksheets in excel at once.

But, there are not many differences between ms excel 2007, 2010, 2013, 2016 and 2019 to run the macro in the VBA.

Does That Code Work To View All Very Hidden Sheets?

Yes, I tried that code too, to my very hidden sheets, and that I've unhidden them all by one code application.

That code was really working to view all very hidden worksheets in excel.

So, that macro excel code can be used to unhide hidden and very hidden worksheets.

That is really helpful to me.

That is macro code to view multiple sheets in excel at once with VBA. That's really work to me.

Share this post