autohide and auto-unhide rows based on a zero value in column a

[mikeb7500]

I have a spreadsheet that I would like to AUTO-HIDE, AND AUTO-UN-HIDE the row based if the Column 1 value in that Row s 0. I have 20 Rows dispersed throughout 100 rows I would like to do this with, so that A1=1, Row A will auto=unhide, but if I have A1=0(or not answered yet), Row A will be hidden.

This is for a question where I ask:

Do you have any children? If the answer is YES, I expose the next few Rows that ask questions about the children, like name etc because the value in Column one is 1.

But if the answer is NO (or, not answered yet), those Rows remain hidden. (YES=1, NO=0) No children, no information needed

So the first cell in each row will have a 0 or a 1 in it.

I felt this one the easiest way to do it, since I have several questions, that the Rows that follow each question will either remain hidden, or unhide when the value is 1 so I can ask the follow-up questions.

Thanks for your help

 

[Jeffrey Mahoney]

You need to put this in the Sheet Module

I created a named range called 'Children' and a named range called 'Choice2'. These are the cells that contain the choices made. You can name them anything. The red numbers below are how many rows below each question you want to hide and unhide +1. In My example, 4 represents 3 rows below the question.

All you need to to is copy the code and replace the named ranges with your own that you create.

Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Not Intersect(Target, Range("Children")) Is Nothing Then
    If Range("Children").Value = 1 Then
      Range(Range("Children").Offset(1, 0), Range("Children").Offset([COLOR=#ff0000]4[/COLOR], 0)).EntireRow.Hidden = False
    ElseIf Range("Children").Value = 0 Then
      Range(Range("Children").Offset(1, 0), Range("Children").Offset([COLOR=#ff0000]4[/COLOR], 0)).EntireRow.Hidden = True
    End If
  End If
  
  
  If Not Intersect(Target, Range("Choice2")) Is Nothing Then
    If Range("Choice2").Value = 1 Then
      Range(Range("Choice2").Offset(1, 0), Range("Choice2").Offset(4, 0)).EntireRow.Hidden = False
    ElseIf Range("Choice2").Value = 0 Then
      Range(Range("Choice2").Offset(1, 0), Range("Choice2").Offset(4, 0)).EntireRow.Hidden = True
    End If
  End If
  
      
End Sub

 

[mikeb7500]

Thanks Jeff, but I couldn't get it to work. AND IT DID HELP BY TELLING ME WHERE TO PASTE IT, IN THIS CASE A MODULE. Please let me simplify.

If Cell A2="1" Show the 2 Row. If it's NOT a "1", (ZERO or anything else) Hide the A Row.

If Cell A3="1" Show the 3 Row. If it's NOT, a "1" (ZERO or anything else) Hide the 1 Row.

I'll be controlling weather Column A, Rows 1-100, has a 1 or 0 in them, by entering the one or zero in a static cells, on Rows that will never be hidden.

So Example,

I enter in J1, a "1"
Because A2=J1, it results in a "1" in Cell A2, thus Row2 now UNHIDES.
By default, anything other than a 1 would result in the Row 2 remaining hidden.
It's important to control each Row individually.

Thanks again!

 

[Jeffrey Mahoney]

I want to make sure that you pasted that code in a SHEET module. You should find your sheet module under 'Microsoft Excel Objects' and it should show you the name of the sheet on which you are trying to hide these rows.

I believe I understand your request. The code I gave you is a framework for what you are trying to accomplish. You would need to alter the number of rows below each cell you want to change from zero to one and vice-versa by changing the red numbers that I described. I assumed that the number of alternate questions below each main question would be different for each main question. For instance: 'Do you have any children' would unhide one row to only ask how many children; but asked if they 'own their own house', it may unhide two rows to reveal questions like 'How long have you owned', or 'How many bedrooms'. And then even other possibilities such that if they answered no to the question, it may inhide rows pertaining to Renting. Anyway...

The reason I use Named Ranges is because it's easy to reference them in the VBA code; meaning, if you move cells around, you don't don't have to alter the code every time.

The code I gave you relies on a user manually entering a value in the named range cell. It will not be triggered by the formula in a cell changing the result from one to zero. So, your example above where cell A2 references cell J1 will not work.

Jeff

 

[mikeb7500]

Jeff, this is what I did. I marked the changes in RED. The cell I'm entering the 1 or 0 in, is K9. There are 4 rows I want to hide and unhide, so the 4's have been changed to 5's. I opened the VBA and double clicked on the Sheet, it says General and Declarations at the top... the window name is Electronic Survey. xlsm. We're close but it's not working yet...it looks like this. Please advise...and as always THANKS! (I haven't tackled Choice 2 yet.)

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("K9")) Is Nothing Then
If Range("K9").Value = 1 Then
Range(Range("K9").Offset(1, 0), Range("K9").Offset(5, 0)).EntireRow.Hidden = False
ElseIf Range("K9").Value = 0 Then
Range(Range("K9").Offset(1, 0), Range("K9").Offset(5, 0)).EntireRow.Hidden = True
End If
End If


If Not Intersect(Target, Range("Choice2")) Is Nothing Then
If Range("Choice2").Value = 1 Then
Range(Range("Choice2").Offset(1, 0), Range("Choice2").Offset(4, 0)).EntireRow.Hidden = False
ElseIf Range("Choice2").Value = 0 Then
Range(Range("Choice2").Offset(1, 0), Range("Choice2").Offset(4, 0)).EntireRow.Hidden = True
End If
End If


End Sub

 

[Jeffrey Mahoney]

So, as long as that code has been pasted in the Sheet module named after your sheet, then it should work. When you click on cell K9 and change the value from zero to one or vice-versa, it should toggle the 4 rows visibility below cell K9.

Oh, and you have to have your macros enabled.

 

[mikeb7500]

I enabled my Macros, by going to Options then Trust Center, and when I tried to run it, this line came up in YELLOW.

Range(Range("K9").Offset(1, 0), Range("K9").Offset(5, 0)).EntireRow.Hidden = True

Doesn't work. So imagine this is a bug.

 

[mikeb7500]

Hey Jeff,

(AS AN OPTION)

I came across this code, and it accomplishes exactly what I need, except I have to run it manually. Is there a way to modify THIS code so it will run automatically when the change is made to the cell "K9"? (Keep in mind this question is coming from someone with very minimal knowledge on VBA Code) Thanks!

Sub HideRows()
Application.ScreenUpdating = False
Application.Calculation = xlManual

Rows("10:14").Hidden = False

For Each c In Range("A10:A14")
If c.Value = 0 And c.Value <> "" Then Rows(c.Row).Hidden = True
Next

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

 

[Trebor76]

Hi mikeb7500,

It seems that the value in cell K9 is the result of a formula. If so the worksheet_change event doesn't capture the change - you need the worksheet_calculate event like so (I've added comments to help explain what the code is doing):

Option Explicit
Private Sub Worksheet_Calculate()

    With Application
        .ScreenUpdating = False 'Turn screen updating off
        .EnableEvents = False 'Turn macros off (recommended for worksheet_event macros as it stops the code getting stuck in a loop)
    End With

    'If the calculated value in cell K9 is zero, then...
    If Val(Range("K9")) = 0 Then
        '...hide rows 10 to 14 (inclusive)
        Rows("10:14").Hidden = True
    'Else, if the calculated value in cell K9 is one, then...
    ElseIf Val(Range("K9")) = 1 Then
        '...unhide rows 10 to 14 (inclusive)
        Rows("10:14").Hidden = False
    End If
    
    With Application
        .ScreenUpdating = True 'Turn screen updating on
        .EnableEvents = True 'Turn macros on
    End With

End Sub

Regards,

Robert

 

[mikeb7500]

Thanks for jumping in Robert, it works GREAT. Until I protect the worksheet(no password). Then I get this message:

Run-time error 1004

Unable to set the Hidden property of the Range class

I read another thread where this question is asked, and it says to do the following, but not sure what to do with it (I know that the word "hello" is for the password, but I don't need one). Could you please assist in either entering this line, or if you no a better way, it's all up to you. We are ALMOST there... Thanks!

"If the sheet is protected, including the following line at the beginning of your code should help:

Code:
ActiveSheet.Unprotect ("hello")