PDA

View Full Version : A macro to show and hide specific rows in specific worksheets



robinho0d
02-08-2023, 02:19 AM
Hello, everyone
I didn't know whether to ask here or ask a brand new question, because what I was looking for corresponds to 90% of the macro made.
My query is (if you say I will start a new topic with the question and link to this topic) can I do exactly the same idea, but make the macro so that it works in the same way, but for each selected worksheet, can to hide different rows.
For example:
London - rows - 25,69,88,89,90,115 etc
Paris - rows - 74,254,255,256,284,293 etc

First macro witch working


Private Sub Workbook_Open()
HideUnhide True End Sub

Public Sub HideUnhide(Optional LockSheet As Boolean)
'LockSheet = true forces sheet(s) to be locked. Use on workbook open.
'LockSheet not specified or false; sheet(s) locked based on button caption.

Dim shp As Object: Set shp = Sheets("Product").Shapes("Button 1").DrawingObject
If Not LockSheet And shp.Caption = "Unhide" Then
If Unhide(InputBox("Enter password"), "London", "NY", "Paris") Then
shp.Caption = "Hide"
End If
Else
If Hide("Plovdiv", "London", "NY", "Paris") Then
shp.Caption = "Unhide"
End If
End If
End Sub

Private Function Hide(pw As String, ParamArray sheetname() As Variant) As Boolean
On Error Resume Next
For Each sht In sheetname
With Sheets(sht)
Select Case Err.Number
Case 0
.Range( _
"4:4,5:5,10:10,11:11,13:13,14:14,17:17,18:18,34:34,35:35,38:38," & _
"45:45,46:46,49:49,50:50,72:72,73:73,76:76,77:77,78:78,79:79" _
).EntireRow.hidden = True

Select Case Err.Number
Case 0, 1004 'already hidden?
Case Else: Debug.Print Err.Number, Err.Description
End Select
Err.Clear

.Protect pw, DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True

Select Case Err.Number
Case Is <> 0: Debug.Print Err.Description
End Select
Err.Clear

Case Else
MsgBox "Sheet " & sht & " not found!"
Err.Clear
End Select
End With
Next
Hide = True
End Function

Private Function Unhide(pw As String, ParamArray sheetname() As Variant) As Boolean
On Error Resume Next
For Each sht In sheetname
With Sheets(sht)
Select Case Err.Number
Case 0
.Unprotect Password:=pw
.Cells.EntireRow.hidden = False
If Err.Number <> 0 Then
MsgBox "Wrong password", vbExclamation
Exit Function
End If
Case Else
MsgBox "Sheet " & sht & " not found!"
Err.Clear
End Select
End With
Next
Unhide = True End Function


and from this second macro is a how to take it line witch say how to write worksheets and rows

Application.ScreenUpdating = False Dim Ary As Variant
Dim i As Long

Ary = Array(London, "26,28,39,142", Paris, "135,147,158,200,201,202", Sheet8, "26:75", Sheet3, "11:239")
For i = 0 To UBound(Ary) Step 2
Ary(i).Rows(Ary(i + 1)).Hidden = False
Next i