A lot of worksheets problem - Hide and Seek

Hi Readers, hope you are enjoying my articles. Few days back I have written an article about how to unhide lot of worksheets with just three line of code. You can find the article here.

However, if you are still with me and your problem is not only unhide sheets but hide them again and the number of sheets are 100 and they are not sitting one by one, then their is a problem. One solution is to go to each sheet and hide them one by one or use Ctrl to select them and hide all together. Now I have seen that using Ctrl to select too many sheets might be cumbersome. Hence their is a solution to this.

I have developed a set of codes that can be used together to solve the above stated issue. The fist set of code is used to identify those sheets that are hidden, store their index in an array and then create a sheet "ddky", paste the value of the array and "very hide" the "ddky" sheet. Their is another way of doing this without using array. I love to work with array and hence I have used it. If you guys have figured it out, let me know in the comment section.

Sub unhide()
Dim ar() As Variant
Dim n As Integer, t As Integer, x As Integer
Dim ws As Worksheet


ReDim Preserve ar(0)
ar(0) = 0
For Each ws In Sheets
    If ws.Name = "ddky" Then GoTo lv
Next


Application.ScreenUpdating = False
For Each ws In Sheets
    n = n + 1
    If ws.Visible = False Then
        ws.Visible = True
        t = t + 1
        ReDim Preserve ar(t)
        ar(t) = n
    End If
Next


x = UBound(ar) - LBound(ar) + 1
Debug.Print x
Set ws = Sheets.Add
ws.Name = "ddky"
ws.Range("a1" & ":a" & x) = Excel.WorksheetFunction.Transpose(ar)
Application.DisplayAlerts = False
If IsEmpty(ws.Range("a2")) Then
ws.Delete
Exit Sub
End If


Application.DisplayAlerts = True
Sheets("ddky").Visible = xlSheetVeryHidden


Application.ScreenUpdating = True
Exit Sub


lv:
MsgBox "You have alredy used me to unhide sheets. Alas, I have nothing to offer more."
End Sub

The second section of the code allow us to hide all the sheets that we have unhided using the above code. Please remember both these codes are complementary to each other. If you don't use the UNHIDE code above you will not be able to use the below hide_again section. However, I have surpassed all possible type of error. I you guys use this and find out any possible error do let me know, I will fix the code.

Sub hide_again()


Dim lr As Long, i As Long
Dim msg As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
msg = MsgBox("If you want to continue press yes, if you press no, I will delete that very very hidden sheet that you have created earlier" & vbNewLine & " because you dont need that sheet anymore", vbYesNo + vbQuestion)


On Error GoTo lv
If msg = vbNo Then 
Application.DisplayAlerts = True
Exit Sub

End If


On Error GoTo lv
lr = Sheets("ddky").UsedRange.Rows.Count




For i = 2 To lr
    Sheets(Sheets("ddky").Cells(i, 1).Value).Visible = False
Next


Application.DisplayAlerts = False
Sheets("ddky").Visible = xlSheetVisible
Sheets("ddky").Delete
Application.DisplayAlerts = True


Application.ScreenUpdating = True
Exit Sub
lv:
MsgBox "There were no hidden sheets or You have not used UNHIDE to unhide your hidden sheets"
Application.ScreenUpdating = True
End Sub

Please remember, even if you don't want to use the "hide_again" you can just press it and say no, this will delete that extra sheet that we have created. If you run this "hide_again" section, the extra sheet will be deleted. So no worry folks just enjoy the beauty of VBA in your daily life.

Please remember do not change position of those sheets. Although we generally don't do that, but if you do these two tools will not give you the desired result.

Here, I am trying to figure out the issues I face and trying to find out a solution. if you guys face any issue, and looking for a solution without digging deep into VBA do let me know. I purposefully avoid explanation of the codes assuming you guys are just looking for a solution. Do let me know if you are looking for an explanation of the code I post.

Save this code in your personal workbook and add to ribbon or quick access tool bar and enjoy.