Hiding/Unhiding sets of rows based off one value specific to each set

0
votes

I have a workbook where sheet 1 is getting inputs from sheet 5 and I want a section of rows on sheet 1 to hide/unhide automatically depending on if something was input on sheet 5. For example, cell D6 on sheet 5 is a name input and when I enter a name (John Smith) that text also populates in cell A5 on sheet 1 with A6:A14 being rows with data concerning John Smith. If sheet 5, cell D6 is blank, I want sheet 1, A5:A14 to automatically hide until I enter a name in D6. And then repeat that same pattern but with different cell references for each section. Like this:

Section 1: Sheet 5 input: D6 Sheet 1: A5 = Sheet5!D6, Rows to hide/unhide: A5:A14

Section 2: Sheet 5 input: F6 Sheet 1: A16 = Sheet5!F6, Rows to hide/unhide: A16:A25

Section 3: Sheet 5 input: H6 Sheet 1: A27 = Sheet5!F6, Rows to hide/unhide: A27:36

And so on for 10 sections and then the pattern changes slightly. But I figure if I can work out how to repeat one pattern, I can then apply the same method to any others.

I found two codes (and modified them a little) on this forum that will work for one section but I can't figure out how to repeat this process for another section (same pattern, different cell references).

1st code:

Option Explicit
Private Sub Worksheet_Activate()
    Dim r As Range, c As Range, h As Range
Set r = Range("a5")
Set h = Range("a5:a14")
Application.ScreenUpdating = False
For Each c In r
    If Len(c.Text) = 0 Then
        h.EntireRow.Hidden = True
    Else
        h.EntireRow.Hidden = False
    End If
Next c
Application.ScreenUpdating = False
End Sub

2nd code

Private Sub Worksheet_Activate()
    Dim r As Range
Set r = Range("a5")
    If Len(r.Text) = 0 Then
        Rows("5:14").EntireRow.Hidden = True
    Else
        Rows("5:14").EntireRow.Hidden = False
    End If
End Sub

I tried adding more ranges like this: Dim r1 As Range, r2 As Range and like this: Dim r(1 To 10) But no luck! I am very new to VBA , so somebody please, please, please tell me what I am doing wrong and how to fix it!

Thank you!!

2
vbaexcel
You have to tell us what you are doing wrong. What exactly is your problem ? Post the code you've tried and the result. – SJR
@SJR I tried so many different variations that I cant really give you an exact code that wasnt working. Basically, I need help building the code from the ground up to do what I described above. My problem is I can figure out a basic setup to hide one section (see codes above) but I don't know how to include all the sections I actually need. – Amanda Johnson

2 Answers

1
votes

Here is one approach - you can add more range addresses to the array v.

Private Sub Worksheet_Activate()

Dim v As Variant, i As Long

v = Array("A5", "A16", "A27")

For i = LBound(v) To UBound(v)
    Range(v(i)).Resize(10).EntireRow.Hidden = (Len(Range(v(i))) = 0)
Next i

End Sub
0
votes

place this code in "Sheet5" code pane

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D6, F6, H6")) Is Nothing Then Exit Sub ' do nothing if changed cells are not relevant

    Dim cell As Range
    For Each cell In Target ' loop through changed cell
        Worksheets("Sheet1").Cells(5 + 0.5 * (cell.Column - 4) * 11, 1).Resize(10).EntireRow.Hidden = IsEmpty(cell) 'change Sheet1 rows visibility according to current changed cell column and value
    Next
End Sub