(Solved) – Attempt to hide rows based on cell value

  • by
(solved)-–-attempt-to-hide-rows-based-on-cell-value

I am having some issues getting my code to work correctly. Please have a look at this code:

Private Sub Worksheet_Calculate()
Dim GenRequire As Integer
GenRequire = Range("A21").Value

Rows("22:31").EntireRow.Hidden = True
Select Case GenRequire
    Case Is = 0: Rows("22:31").EntireRow.Hidden = True 'No Rows shown
    Case Is = 1: Rows("22:22").EntireRow.Hidden = False '1st Row shown
    Case Is = 2: Rows("22:23").EntireRow.Hidden = False '1-2 rows shown
    Case Is = 3: Rows("22:24").EntireRow.Hidden = False '1-3 rows shown
    Case Is = 4: Rows("22:25").EntireRow.Hidden = False '1-4 rows shown
    Case Is = 5: Rows("22:26").EntireRow.Hidden = False '1-5 rows shown
    Case Is = 6: Rows("22:27").EntireRow.Hidden = False '1-6 rows shown
    Case Is = 7: Rows("22:28").EntireRow.Hidden = False '1-7 rows shown
    Case Is = 8: Rows("22:29").EntireRow.Hidden = False '1-8 rows shown
    Case Is = 9: Rows("22:30").EntireRow.Hidden = False '1-9 rows shown
    Case Is = 10: Rows("22:31").EntireRow.Hidden = False '1-10 rows shown
End Select
End Sub

I’m getting an error on Case Is = 10:,

Run-time error 1004:, Application-defined or object-defined error.

I am attempting to have a cell, A21 contain a value between 0-10 (an integer value) where 0 reveals no rows between 22-31, 1 reveals row 22, 2 reveals 22 and 23 and 3 reveals 22, 23, and 24, etc… Any help anyone can provide will be so helpful as I am really new to VBA scripting in Excel. Note: I have office 365 for my Excel version.

Leave a Reply

Your email address will not be published. Required fields are marked *