VBA to hide/unhide multiple rows based on values in multiple cells-CodePudding

Hoping someone may be able to identify what I am doing wrong. I've written VBA to hide/unhide rows based on values in multiple cells when button is clicked, but it's only working on the value in the first cell and not the full range.

My file starts with rows 15:31 hidden. The user is asked to submit their rating from 1 to 5 on rows 6:14 which the result feeds into column A of respective row, hence the case values in the code. If any of the users ratings are less than 4, then rows 15:31 should remain hidden. If they are all 4 or over, then rows 15:22 should unhide. The user then rates themselves from 1 to 5 again on rows 15:22 and if their ratings are less than 4, rows 23:31 remain hidden but if they are all 4 or over, then rows 23:31 will unhide.

Here is my code:-

Sub Submit() 

    Select Case Range("A6,A7,A8,A9,A10,A11,A12,A13,A14") 

        Case Is >= 4: Rows("15:22").EntireRow.Hidden = False 

                      Worksheets("Print Result - Foundation").Visible = False 

            Select Case Range("A15,A16,A17,A18,A19,A20,A21,A22") 

                Case Is >= 4: Rows("23:31").EntireRow.Hidden = False 

                              Worksheets("Print Result - Grow").Visible = False 

                Case Is < 4: Rows("23:31").EntireRow.Hidden = True 

            End Select 

        Case Is < 4: Rows("15:31").EntireRow.Hidden = True 

    End Select 

End Sub

The above works well and hides rows 15:31 when A6 is less than 4 but when A7:A14 is less than 4, it displays rows 15:22. And the same with the second range, when A15 is less than 4 rows 23:31 are hidden but when A16:A22 are less than 4, rows 23:31 are displaying.

I have also tried entering the range as ("A6:A14") and ("A15:A22") but when I try to run the macro, it returns a run-time error '13': Type mismatch.

Can anyone see where I have gone wrong?

CodePudding user response：

I have a working solution in this instance. If you were to increase the number of rows that your user would input reviews, you would be required to update the range to include those values. What I saw you went wrong with was what Ike had seen where your code only considered the first cell in the range rather than each cell in the range, so by looping through each cell for its value you can check each one. I also used the loops rather than Select Case because, out of my own experience, I find it easier to sort by values using if conditionals because I only require one statement.

Sub Submit()
'
' Hide rows with low reviews
'
'Instantiate Ranges

Dim paramRange As Range
    Set paramRange = Range("A6:A14")
Dim condRange As Range
    Set condRange = Range("A15:A22")

'Instantiate counter
Dim i As Integer
    i = 0

'Hide rows before checking ratings
Rows("15:31").EntireRow.Hidden = True

'Iterate through each cell in the paramRange
For Each cell In paramRange

    'Check if rating is 4 or greater
    If cell.Value < 4 Then

        'If lower than 4 exit loop without increasing counter
        Exit For
    Else

    'If is 4 or greater add to counter
    i = i   1
    End If
Next

'If counter is equal to number of cells in the paramRange
If i = paramRange.Count Then

    'Reveal rows 15:22
    Rows("15:22").EntireRow.Hidden = False

    'Reset counter
    i = 0

    'Iterate through each cell in the condRange
    For Each rating In condRange

        'Check if rating is 4 or greater
        If rating.Value < 4 Then
    
            'If lower than 4 exit loop without increasing counter
            Exit For
        Else
    
        'If is 4 or greater add to counter
        i = i   1
        End If
    Next

    'If counter is equal to number of cells in the condRange
    If i = condRange.Count Then

        'Reveal rows 23:31
        Rows("23:31").EntireRow.Hidden = False
    End If
End If

'
End Sub

I hope that this answers your question and can help you out. If not please let me know and I will keep working through it. If better programmers than I find any way to make it work faster/more optimally that would be more helpful, but this works on my end to hide and unhide the selected rows.