I have various choices on a worksheet named "INSERT" that hide rows on a worksheet called "Calculation".

The worksheet "INSERT" is hidden before users can change selections in "Calculation".

Some of these changes are overriding selections in "INSERT", (i.e. some rows that were hidden).

I need to do some sort of IF AND statement but there are 6 options in "INSERT" and therefore my code may end up being very long, unless you could kindly assist me please.

A selection of an idea I have is as follows:

I should also state that within the code for the macros when clicking the buttons within "INSERT" to make selections, I have entered a 1 in a cell underneath the button to show that it was selected (e.g. E19).

Thanking you in advance!

The worksheet "INSERT" is hidden before users can change selections in "Calculation".

Some of these changes are overriding selections in "INSERT", (i.e. some rows that were hidden).

I need to do some sort of IF AND statement but there are 6 options in "INSERT" and therefore my code may end up being very long, unless you could kindly assist me please.

A selection of an idea I have is as follows:

I should also state that within the code for the macros when clicking the buttons within "INSERT" to make selections, I have entered a 1 in a cell underneath the button to show that it was selected (e.g. E19).

```
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.Thanking you in advance!

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

```
Range("D9:G9").Select
ActiveCell.FormulaR1C1 = "1"
Range("D6:G6").Select
ActiveCell.FormulaR1C1 = "0"
```

to

```
Range("D9:G9").FormulaR1C1 = "1"
Range("D6:G6").FormulaR1C1 = "0"
```

This would result in something shorter like:

```
Sheets("Calculation").Unprotect Password:="hello01"
If InitialCondition Then
ElseIf Range("D3").Value = "L" And Sheets("INSERT").Range("E19") = "1" Then
Range("D9:G9").FormulaR1C1 = "1"
Range("D6:G6").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
ElseIf Range("D3").Value = "L" And Sheets("INSERT").Range("E19") = "0" Then
Range("D9:G9").FormulaR1C1 = "1"
Range("D6:G6").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
EndIf
Sheets("Calculation").Protect Password:="hello01"
```

And much better would be using a DSL-approach using simple helper functions. E.g.

```
Public Sub SetValues
UnprotectSheet
If InitialCondition Then
ElseIf Range("D3").Value = "L" And Sheets("INSERT").Range("E19") = "1" Then
SetFormula "1", "0"
SetHidden True, False, False, True, False, True
ElseIf Range("D3").Value = "L" And Sheets("INSERT").Range("E19") = "0" Then
SetFormula "1", "0"
SetHidden False, False, False, True, False, True
EndIf
ProtectSheet
End Sub
Private Sub ProtectSheet()
Sheets("Calculation").Protect Password:="hello01"
End Sub
Private Sub UnprotectSheet()
Sheets("Calculation").Unprotect Password:="hello01"
End Sub
Private Sub SetFormula(F1 As String, F2 As String)
Range("D9:G9").FormulaR1C1 = F1
Range("D6:G6").FormulaR1C1 = F2
End Sub
Private Sub SetHidden(R1 As Boolen, R2 As Boolen, R3 As Boolen, R4 As Boolen, R5 As Boolen, R6 As Boolen)
Rows("6:6").Hidden = R1
Rows("9:10").Hidden = R2
Rows("11:11").Hidden = R3
Rows("13:13").Hidden = R4
Rows("22:22").Hidden = R5
Rows("32:34").Hidden = R6
End Sub
```

Just name the helper methods and its parameters according to its real content..

```
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
```

In summary:

You have six cells in row 19 of the "INSERT" sheet that can have a value of 0 or 1. Your code gives an example of what to do depending on the value of one of the cells. The code for each choice is a bit over 20 lines long. These two pieces of code are very similar.

One way to approach it would be derive a single number from the choices and to write a single Select Case/End Select statement with a Case block for each value. Unfortunately the number of possible values (64) is even higher that you thought.

Here is some code to derive the value:

```
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
```

We don't know how the values interact, or how much duplication there is in the desired code for each block. Code that is common to many choices could be put in a separate procedure. I think that might be the same as or a similar point to what ste5an is referring to. (I'm not sure what DSL is).

In the case of code like this

```
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
```

We could have a procedure which takes an integer value and does the reverse of the GetButtonsValue function.

```
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
```

It isn't clear whether any or all the target rows are to be unhidden by default. If necessary they could be all unhidden at the start of the process. The code is untested.

```
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
```

I have recommended this question be closed as follows:

Accept: GrahamSkan (https:#a42081454)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

MacroShadow

Experts-Exchange Cleanup Volunteer