Hide rows based on cell values

**mlb830** · 06-18-2009, 07:08 AM

Hello,
VBA novice here, and hopefully someone can help!
Writing a tax compilation sheet for preparers - users have to fill in Cells A10, B10, and C10 with either "yes" or "no" from a drop-down data validation list.

If users select "yes" for all 3 cells ---> rows 150 - 200 will be displayed. If any of the cells are "no", then I want the rows 150 - 200 to be hidden.
I've been successful with the following code for one y/n question, but it won't work for the 3 criteria test:

If ActiveCell.Offset(columnOffset:=1) = "cond1" Then
    If ActiveCell = "Yes" Then
        Rows("27:38").Hidden = False
       Else
         Rows("27:38").Hidden = True  
    End If
End If

Any insight is appreciated, and thanks in advance

**DonkeyOte** · 06-18-2009, 07:16 AM

mlb830, welcome to the Board - please take the time to read the forum rules.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**mlb830** · 06-18-2009, 01:51 PM

My apologies,
Code is as follows:

If ActiveCell.Offset(columnOffset:=1) = "cond1" Then
    If ActiveCell = "Yes" Then
        Rows("27:38").Hidden = False
       Else
         Rows("27:38").Hidden = True
    
    End If
End If

Thanks!

**Larry.LeBlanc@O** · 06-18-2009, 04:19 PM

This works. Convert to lower case in case user enters "Yes":

Sub test()
 If (LCase(Range("A10").Value) = "yes" And _
     LCase(Range("B10").Value) = "yes" And _
     LCase(Range("C10").Value) = "yes") Then
                    Rows("15:20").Hidden = False  'I use 15:20 for easier de-bugging than 150:200
Else
                    Rows("15:20").Hidden = True
End If

End Sub

**DonkeyOte** · 06-18-2009, 05:05 PM

Perhaps you mean along the lines of:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A10:C10")) Is Nothing Then Exit Sub
Rows("150:200").Hidden = Application.CountIf(Range("A10:C10"), "Yes") < 3
End Sub