Preventing Rows from being hidden based on cells not empty-CodePudding

I am needing to prevent a range of rows from being hidden if any of the rows have text in cells in D column.

Private Sub Worksheet_Change(ByVal Target As Range)
Set rRng = Sheet1.Range("D35, D36, D37, D38, D39, D40")

If Target.Row >= 34 And Target.Row <= 40 Then
    
    If IsEmpty("rRng.value") Then
    Range("D35:D40").EntireRow.Hidden = False
    
    Else: Range("D35:D40").EntireRow.Hidden = (Range("D34").Value = "")
    End If
End If

End Sub

CodePudding user response：

You might need to trigger your hide/unhide from the calculate event too so that any formulas/vba that changes the values also triggers and update. (Im not sure what the intended result is, so the code below isn't a dropin replacement)

Private Sub Worksheet_Calculate()
HideRows
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
HideRows
End Sub

Public Sub HideRows()
Dim R As Range: Set R = Sheet1.Range("D35:D40")
If Excel.WorksheetFunction.CountA(R) > 0 Then
    R.EntireRow.Hidden = False
Else
    R.EntireRow.Hidden = True
End If
End Sub

Sub SetAValue()
Sheet1.Range("D35").Value = "show"
End Sub

CodePudding user response：

Nick, Thank you much! I revised your code to the following and it works prefect. I apologize for not being able to post the revised code. I have tried multiple time and it does not post correctly