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

Question

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?

Using the range like you do it within the select case only evaluates the first cell (A6). What do you want to achieve: if any of the cells is >=4? — Ike
– Ike, Commented Jul 19, 2022 at 11:09
For your first test - Case Is >= 4 - did you mean that all of those cells are >= 4, or any one of them is >= 4, or the SUM total of those cells >= 4 ? — CLR
– CLR, Commented Jul 19, 2022 at 11:20
If any of the cells are >= 4 in the range A6:A14, I want rows 15:22 to unhide. If any of the cells in that range are <4, I want rows 15:31 to remain hidden. Again, if any of the cells in range A15:A22 are >= 4, I want rows 23:31 to unhide, and if any of the cells are <4, I want the rows to remain hidden. — FionaL
– FionaL, Commented Jul 19, 2022 at 20:31

Brody Toth · Accepted Answer · 2022-07-19 16:09:12Z

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.

I knew I was missing something. Thanks Brody Toth, this worked a charm

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related