Any way to reduce the number of lines in my code?-CodePudding

I have this code in Excel VBA where I try to hide the rows that have no data in them.

Private Sub CheckBox3_Click()
If CheckBox3 = True And IsEmpty(Range("A12")) = False And IsEmpty(Range("A13")) = False And IsEmpty(Range("A14")) = False Then
Rows("12:14").Hidden = False
ElseIf CheckBox3 = True And IsEmpty(Range("A12")) = True And IsEmpty(Range("A13")) = False And IsEmpty(Range("A14")) = False Then
Rows("12").Hidden = True
Rows("13:14").Hidden = False
ElseIf CheckBox3 = True And IsEmpty(Range("A12")) = True And IsEmpty(Range("A13")) = True And IsEmpty(Range("A14")) = False Then
Rows("12:13").Hidden = True
Rows("14").Hidden = False
ElseIf CheckBox3 = True And IsEmpty(Range("A12")) = True And IsEmpty(Range("A13")) = False And IsEmpty(Range("A14")) = True Then
Rows("12").Hidden = True
Rows("13").Hidden = False
Rows("14").Hidden = True
ElseIf CheckBox3 = True And IsEmpty(Range("A12")) = False And IsEmpty(Range("A13")) = True And IsEmpty(Range("A14")) = True Then
Rows("12").Hidden = False
Rows("13:14").Hidden = True
ElseIf CheckBox3 = True And IsEmpty(Range("A12")) = False And IsEmpty(Range("A13")) = True And IsEmpty(Range("A14")) = False Then
Rows("12").Hidden = False
Rows("13").Hidden = True
Rows("14").Hidden = False
ElseIf CheckBox3 = True And IsEmpty(Range("A12")) = False And IsEmpty(Range("A13")) = False And IsEmpty(Range("A14")) = True Then
Rows("12:13").Hidden = False
Rows("14").Hidden = True
Else
Rows("12:14").Hidden = True
End If
End Sub

I want to make it to use 5 variables but that will mean that I have to use 32 combinations.

Any way to make it shorter?

CodePudding user response：

this should work for you:

Sub CheckBox3_Click()
    Dim ranges(1 To 3) As Range
    Dim i As Long
    
    i = 1
    Set ranges(1) = Range("A12")
    Set ranges(2) = Range("A13")
    Set ranges(3) = Range("A14")
    
    If CheckBox3 Then
        For i = i To 3
            If IsEmpty(ranges(i)) Then
                ranges(i).Rows.Hidden = True
            Else
                ranges(i).Rows.Hidden = False
            End If
        Next i
    End If
    
End Sub