VBA Hiding Rows Based on Row Above Value

Question

I'm new to VBA coding within Excel and for the most part I've been able to find and use coding that will allow my workbook to do what I need it to do. I've come across a section that I'm trying to hide rows until the row above has a value entered.

I have VBA coding that will hide the all of the rows that I have marked with the red parenthesis based on the value within B75 ("" and "No"). If value is changed to "Yes", it will unhide all rows (76 to 116). But, what I'm really trying to do is only unhide one row at a time. When B75's value switches to Yes, I want it to really only unhide Row 77. When A77 has a value entered, I want row 78 to unhide, so and and so forth. Also, I should mention, Column A is a free form field. The unhide process would repeat until I reach the end of my designated rows for this section (Row 116). Row116 would not need to unhide the next row. The code for the value change in B75 does not include Row117.

I've attached a screen print of my workbook so you can have a visual of what I'm working with.

workbook

The code I'm currently using for the value change of B75 is below:

If Range("B75").Value = "" Then
    Rows("76:116").EntireRow.Hidden = True
ElseIf Range("B75").Value = "No" Then
    Rows("76:116").EntireRow.Hidden = True
ElseIf Range("B75").Value = "Yes" Then
    Rows("76:116").EntireRow.Hidden = False
End If

I'm not sure if the above code needs edited to only unhide one row at a time and it just repeats or if I need a completely different code to only unhiding one row at a time. Any insight would be greatly appreciated!

What's the logic? B75 changes --> row 77 hidden but A77 changes --> row 78 hidden? What happens if you change a row again? — SJR
B75 changes to "Yes" > row 77 unhidden, A77 value entered > row 78 unhidden. If B75 changes to "" or "No", it would hide all rows 76:116. — Bo O.
What if rows are unhidden and a previously-entered value is deleted in (eg) A79 (ie. in the middle of a bunch of unhidden rows) ? I think you might be making your life more complicated than it needs to be — Tim Williams
I see what you're saying- that would be a highly unlikely scenario with this template I'm creating and the use case of it. But, ideally, if A79 is deleted, then I would want to hide Row 79; if A80 has a value in it, I wouldn't want Row 80 hidden because A79 no longer has a value. What I'm trying to accomplish is this long list of blank rows that may or may not be applicable depending on the situation. Some of our clients may use 500 Custom Fields, some use 2. I don't want to unhide all rows making the workbook much longer than it needs to be. — Bo O.

Tim Williams Tim Williams · Accepted Answer · 2020-08-26T01:34:36

You can use the worksheet_change event to monitor changes in B75 and Column A:

Const NUM_ROWS As Long = 20  '<<how many rows to check for hide/unhide

Private Sub Worksheet_Change(ByVal Target As Range)
    'change is in the range being monitored?
    If Not Application.Intersect(Target, _
          Application.Union(Me.Range("B75"), Me.Range("A76").Resize(NUM_ROWS))) Is Nothing Then
        
        CheckRows
    
    End If
End Sub

Sub CheckRows()
    Dim c As Range, rngRows As Range, rngHide As Range, i As Long, arr, bOneVis, showRows As Boolean
    
    Set rngRows = Me.Range("A76").Resize(NUM_ROWS) 'cells in ColA to be checked
    Application.ScreenUpdating = False             'wait till done to update screen
    showRows = Me.Range("B75").Value = "Yes"       'showing any rows?
    rngRows.EntireRow.Hidden = Not showRows        'hide or show all rows
    
    If Not showRows Then Exit Sub                  'nothing else to check
    
    arr = rngRows.Value 'faster than accessing each cell value one-by-one
    For i = UBound(arr, 1) To 2 Step -1
        If Len(arr(i, 1)) = 0 Then 'no value in ColA
            If Len(arr(i - 1, 1)) = 0 Or bOneVis Then
                rngRows.Cells(i).EntireRow.Hidden = True 'Hide if row above is empty
                                                         '  or one empty row is already available
            Else
                bOneVis = True 'at least one row is unhidden for entry
            End If
        End If
    Next i
    'deal with the first row
    If Len(arr(1, 1)) = 0 And bOneVis Then rngRows.Cells(1).EntireRow.Hidden = True
End Sub

VBA Hiding Rows Based on Row Above Value

1 Answers