How to unhide rows based on ANY of several criteria?

Question

An IF function checks whether the cells in any of the rows in the chosen column of the table is filled in, e.g. Column 1, row 1, 2 and 3 - if any of the cells is filled in - additional rows are unhidden.

The function works when the table is getting filled in but only until something gets deleted from it.

e.g. if the table has all the rows 1, 2 and 3 in column 1 filled in - additional rows appear. If I delete one of the cells values e.g. column 1 row 1 - the additional row hides again. The criteria for it to be unhidden is still there, rows 2 and 3 are still filled in.

Private Sub Worksheet_Change(ByVal Target As Range)    
    If Target.Column = 2 And Target.Row = 90 Then    
        If Target.Value = "" Then    
            Application.Rows("94:101").Select   
            Application.Selection.EntireRow.Hidden = True    
            Else: [94:101].EntireRow.Hidden = False    
        End If    
    End If

    If Target.Column = 2 And Target.Row = 91 Then    
        If Target.Value = "" Then    
            Application.Rows("94:101").Select    
            Application.Selection.EntireRow.Hidden = True    
            Else: [94:101].EntireRow.Hidden = False    
        End If    
    End If

    If Target.Column = 2 And Target.Row = 92 Then    
        If Target.Value = "" Then    
            Application.Rows("94:101").Select    
            Application.Selection.EntireRow.Hidden = True    
            Else: [94:101].EntireRow.Hidden = False    
        End If    
    End If    
End Sub

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Sep 11 '19 at 08:41

David García Bodego · Answer 1 · 2019-09-11T10:54:15.747

0

Check @PEH comment first. Following your philosophy, try this one:

Private Sub Worksheet_Change(ByVal Target As Range)    

With WorkSheets(1)
    If (.Range("B90").Value2 <> "") Or (.Range("B91").Value2 <> "") Or _
                       (.Range("B92").Value2 <> "") Then    
        If Target.Value = "" Then    
            .Rows("94:101").EntireRow.Hidden = True    
        Else
            .Rows("94:101").EntireRow.Hidden = False    
        End If    
    End If  
End With  
End Sub

Just custom the WorkSheets(1) to your WorkBook.

edited Sep 11 '19 at 10:54

answered Sep 11 '19 at 09:18

David García Bodego

1,058
3
13
21

@Reh thank you for your help. I will definitely go through the info mentioned in the link. – Catherine Sep 11 '19 at 09:29
David, thank you for the reply! The code definitely looks much better and professional. However there still is the same problem. e.g. if I put something in the row 90 and 91 - the 94:101 appear. But if I delete e.g. 90 (91 stays so 94:101 should stay as well) - 94:101 are hidden again... – Catherine Sep 11 '19 at 09:31
OK. As per your condition, as soon as one of the values on B90, B91 or B92 is "", then everything will be hidden. So the problem is the code. What do you want exactly? If all the cells have a value, it will work, if one have no value (""), then enerything will be hidden. That is your intention? – David García Bodego Sep 11 '19 at 09:46
Is there any order? I mean, if B90 is "" but B91 and/or B92 have any value, then everything should be shown? – David García Bodego Sep 11 '19 at 09:48
I want it to work as following: if at least one of B90, B91 or B92 is not "" -> 94:101 appear. If all of them are empty -> 94:101 are hidden. – Catherine Sep 11 '19 at 09:52
So using your code version I've modified it like this - ``Private Sub Worksheet_Change(ByVal Target As Range) If (Target.Column = 2) And ((Target.Row = 90) Or _ (Target.Row = 91) Or (Target.Row = 92)) Then If Target.Value = "x" Then Application.Rows("94:101").Select Application.Selection.EntireRow.Hidden = False Else: [94:101].EntireRow.Hidden = True End If End If End Sub`` – Catherine Sep 11 '19 at 10:10

mits · Accepted Answer · 2019-09-11T10:59:06.140

0

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 2 And (Target.Column = 90 Or Target.Column = 91 Or Target.Column = 92) Then
        If Range("B90") = "" And Range("B91") = "" And Range("B92") = "" Then
            Range("A94:A101").EntireRow.Hidden = True
        Else
            Range("A94:A101").EntireRow.Hidden = False
        End If
    End If
End Sub

edited Sep 11 '19 at 10:59

answered Sep 11 '19 at 10:04

mits

876
3
11
20

I have adjusted it to my table - changed Target.Row to Target.Column and vice versa - and it worked great. Thank you very much! – Catherine Sep 11 '19 at 11:04

How to unhide rows based on ANY of several criteria?

2 Answers2