ElseIf Range("D3").Value = "L" And Sheets("INSERT").Range("E19") = "1" Then
Sheets("Calculation").Unprotect Password:="hello01"
Range("D9:G9").Select
ActiveCell.FormulaR1C1 = "1"
Range("D6:G6").Select
ActiveCell.FormulaR1C1 = "0"
Rows("6:6").Hidden = True
Rows("9:10").Hidden = False
Rows("11:11").Hidden = False
Rows("13:13").Hidden = True
Rows("22:22").Hidden = False
Rows("32:34").Hidden = True
Sheets("Calculation").Protect Password:="hello01"
ElseIf Range("D3").Value = "L" And Sheets("INSERT").Range("E19") = "0" Then
Sheets("Calculation").Unprotect Password:="hello01"
Range("D9:G9").Select
ActiveCell.FormulaR1C1 = "1"
Range("D6:G6").Select
ActiveCell.FormulaR1C1 = "0"
Rows("6:6").Hidden = False
Rows("9:10").Hidden = False
Rows("11:11").Hidden = False
Rows("13:13").Hidden = True
Rows("22:22").Hidden = False
Rows("32:34").Hidden = True
Sheets("Calculation").Protect Password:="hello01"
The above code is for only 1 of the 6 choices so you can see why it'd end up being very long without a shortcut you may know of.If Range("D3").Value = "L" Then
Select Case Sheets("INSERT").Range("E19")
Case 0 To 5
Sheets("Calculation").Unprotect Password:="hello01"
Range("D9:G9").Select
ActiveCell.FormulaR1C1 = "1"
Range("D6:G6").Select
ActiveCell.FormulaR1C1 = "0"
Rows("6:6").Hidden = False
Rows("9:10").Hidden = False
Rows("11:11").Hidden = False
Rows("13:13").Hidden = True
Rows("22:22").Hidden = False
Rows("32:34").Hidden = True
Sheets("Calculation").Protect Password:="hello01"
End Select
End If
Function GetButtonsValue() As Integer
Dim i As Integer 'power
Dim c As Integer 'column
Dim v As Integer 'cumulative value
With Sheets("INSERT")
For c = 2 To 7
i = c - 2
If .Cells(19, c) = 1 Then
v = v + 2 ^ i
End If
Next c
End With
GetButtonsValue = v
End Function
Rows("6:6").Hidden = True
Rows("9:9").Hidden = False
Rows("10:10").Hidden = True
Rows("11:11").Hidden = False
Rows("13:13").Hidden = True
Rows("22:27").Hidden = False
Rows("32:34").Hidden = True
Sub SetHiddenRows(iHidden As Integer)
Rows("6:6").Hidden = 1 And iHidden
Rows("9:9").Hidden = 2 And iHidden
Rows("10:10").Hidden = 4 And iHidden
Rows("11:11").Hidden = 8 And iHidden
Rows("13:13").Hidden = 16 And iHidden
Rows("22:27").Hidden = 32 And iHidden
Rows("32:34").Hidden = 64 And iHidden
End Sub
Sub HideRows()
Dim LRows() As Variant
Dim HPRows() As Variant
Dim LoRows() As Variant
Dim HideRows() As Variant
Dim c As Integer
HideRows = Array(9, 11, 12, 10, 31, 13) 'list of target rows (less 34 to 36)
LRows = Array(1, 1, 1, 1, 1, 0)
HPRows = Array(1, 0, 1, 0, 1, 1)
LoRows = Array(0, 0, 1, 0, 1, 1)
Select Case Range("D3").Value
Case "L"
Range("D9:G9").FormulaR1C1 = "0"
Range("D6:G6").FormulaR1C1 = "0"
Rows("6:6").Hidden = True
Rows("13:13").Hidden = True
Rows("32:33").Hidden = True
For c = 0 To 5
If LRows(c) = 1 Then
If Sheets("INSERT").Range.Cells(19, c + 2) = "1" Then
Rows(HideRows(c)).Hidden = True
If c = 3 Then
Rows("34:36").Hidden = True 'special for L,E19
End If
End If
End If
Next c
Case "HP"
Range("D9:G9").FormulaR1C1 = "1"
Range("D6:G6").FormulaR1C1 = "0"
dws.Rows("10:11").Hidden = True
dws.Rows("26:27").Hidden = True
dws.Rows("34:36").Hidden = True
For c = 0 To 5
If HPRows(c) = 1 Then
If Sheets("INSERT").Range.Cells(19, c + 2) = "1" Then
Rows(HideRows(c)).Hidden = True
End If
End If
Next c
Case "Lo"
Range("D9:G9").FormulaR1C1 = "0"
Rows("6:6").Hidden = True
Rows("9:11").Hidden = True
Rows("13:13").Hidden = True
Rows("22:22").Hidden = True
Rows("26:27").Hidden = True
Rows("33:36").Hidden = True
For c = 0 To 5
If LoRows(c) = 1 Then
If Sheets("INSERT").Range.Cells(19, c + 2) = "1" Then
Rows(HideRows(c)).Hidden = True
End If
End If
Next c
End Select
End Sub
Also place the Unprotect and Protect before and after your If-block. Then you only need each call once.
And use direct manipulation of objects where possible. Reduce
Open in new window
to
Open in new window
This would result in something shorter like:
Open in new window
And much better would be using a DSL-approach using simple helper functions. E.g.
Open in new window
Just name the helper methods and its parameters according to its real content..