Use Macro to Unhide Rows

JKD_Excel

New Member
Joined
Aug 13, 2019
Messages
1
I’m new to macros and am NOT a programmer. I can use the “Record Macro” function and assign to option buttons to either hide or unhide pretty easily, but I’m struggling to get it to do what I want.

I am trying to create a series of yes/no questions where the answer to the first question determines what question is revealed next, and the answer to that question dictates what question is revealed next. I’m trying to accomplish this by starting with most rows hidden and then unhiding certain rows with each answer using macros assigned to each option button.

I’ve tried using a text button labeled “refresh” to hide all but the first question, say rows 6-100. This would be the starting point for the end-user, but also give them the ability to start over (at least that’s the intent). I want row 6 to appear (unhide) if the first question was answered with a no and row 7 to appear (unhide) if the first question was answered with a yes. And so on...

It’s not working as intended. Any suggestions on how to make this work using macros or by utilizing another function in excel? I’d like the end result to show the series of questions that were presented and answered without blank (or seemingly blank) rows in between where questions were not applicable, so I don’t think conditional formatting is an option...
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
create multiple sub procedure for each question.

then lastly use if , else, elseif, end if. That should do the trick.
 
Upvote 0
One way to approach this is to use a (hidden) helper column ("H")

- each cell H5:H100 is either empty or Y (= hide this row)
- initially the values in H6:H100 are set to Y
- as each question is answered use VBA to amend values in column H (using Worksheet_Change event) - see below
- then a simple macro hides \ unhides as required

Assuming that the helper column is column H ...
Code:
Sub HideUnhide()
    Dim rngHide As Range, rngUnhide As Range, cel As Range
    Set rngHide = Range("H5:H100")
    Set rngUnhide = Range("H4")                 'any permenantly visible cell above H5
    rngHide.EntireRow.Hidden = True
    For Each cel In rngHide
        If cel <> "Yes" Then Set rngUnhide = Union(rngUnhide, cel)
    Next
    rngUnhide.EntireRow.Hidden = False
End Sub

Worksheet_Change
- answering each question needs to drive Worksheet_Change event
eg if the answer to first question is found in D5

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("H")) Is Nothing Then Exit Sub    
    Const Y = "Y", B = ""
    Dim v As Variant:   v = LCase(Target.Value)
    
    Select Case Target.Address(0, 0)
        Case "D5"
            If v = "no" Then
                [H6] = B
            ElseIf v = "yes" Then
                [H7] = B
            End If
        Case "D6"
            '... etc
    End Select
    
    Call HideUnhide
End Sub

Code required for EVERY question to delete the "Y" flag in column H
- is there a pattern (or several patterns) that can be used to reduce coding ?
- eg instead of hard coding [H6] could use Target.Offset(1,4) where the same pattern applies to several answers
Code:
   Case "D5", "D10", "D20"
            If v = "no" Then
                Target.Offset(1,4) = B
            ElseIf v = "yes" Then
                Target.Offset(1,5) = B
            End If

If you need help with the VBA driving values in column H, then
- provide details of what should happen when the first 6 questions are answered
- for each question I need to know
1. which answers are possible
2. where the values are entered
3. which rows are hidden and \ or made visible as a result

and if there are more complicated questions affecting more than 2 rows, provide an example
 
Last edited:
Upvote 0
Oops, I forgot to amend something in Sub HideUnhide

Code:
If cel <> "Yes" Then Set rngUnhide = Union(rngUnhide, cel)

should be

Code:
If cel <> "[COLOR=#ff0000]Y[/COLOR]" Then Set rngUnhide = Union(rngUnhide, cel)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top