VBA - multiple options affecting IF

Hard to tell, but you have some redundancy in your code. So why not setting the default befor the If-block and then only setting the differences in the Then-branches?

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"

Select all Open in new window

to

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

Select all Open in new window

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"

Select all Open in new window

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		

Select all Open in new window

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

Hard to guess your exact situation, but if the only differences are in the value of  Sheets("INSERT").Range("E19"), and you need six such values then you could do something like

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

Select all Open in new window

Sorry, there's a lot of code and I tried to strip out the relevant part.

I have copied the relevant section into the attached document.

Buttons on 'INSERT' will change the corresponding cell in row 19.
Then this sheet will be hidden, but I want the choices in this sheet to be maintained.

The way I see it is that there are 6 choices, but therefore 36 combinations of the different choices.

Thanks ste5an but I don't know enough VBA for this to make total sense to me.  
Thanks GrahamSkan - hopefully the attached and above will help explain my predicament.
TestJC.xlsm

The documents helps a bit in understanding.

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

Select all Open in new window

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

Select all Open in new window

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

Select all Open in new window

I have uploaded the commonalities and differences so it's really clear.
In the table on the right is all the rows that are to be hidden depending on selections in 'Calculation' cell D3.
The table on the left shows those rows that should hidden by the buttons but NOT be overwritten by changing selections in D3.
Code1.xlsx
TestJC.xlsm

Sorry about the delay. Personal things have taken my time in the last few days.

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

Select all Open in new window

No problem Graham, hope everything's ok.

The rows are solely dependant on the selections in the code above so no default as such.

I have deciphered what your code is trying to achieve, not having any prior understanding of arrays.  I've copied and pasted it into the test file, and changing cell D3 has no effect on hiding any rows, regardless of any button selection in the 'Insert' sheet.

As I said, I can't see what bit of code is preventing this from working, as I'm very new to this. Your help is appreciated.

I've attached the file with the code in.

Thanks.
TestJC.xlsm

Sorry, I can't test it. The code fails at the first  attempt to hide a row. I think it is because the sheet is protected.

Sorry, now with unprotect/protect code.
TestJC.xlsm

No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

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