A way to hide rows based on value from other sheet.

[hreyo25]

Hello I am trying to find out the best way to hide a set of rows based on a value from another sheet. The problem is that it cant be a known set of rows because this sheet may have rows added to it. If anyone can help that would be greatly appreciated.

 

[SirJB7]

Hi, hreyo25!
Would you please elaborate a bit more?
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the red sticky posts at this forums main page for uploading guidelines.
Regards!

 

[hreyo25]

Sorry I am trying to relearn VBA for my new job. I am making a datasheet where a user inputs all their data on one sheet and then this data is rearranged on another sheet. What I want to do is if a user selects a "Electronic" signal source on the first sheet, then rows 30-35 hide on the output sheet. I hope this is alittle bit clearer. Thanks

 

[SirJB7]

Hi, hreyo25!
Yes, that's much clearer but a detailed description of both worksheets layouts or better a sample file will help to suggest a better solution. Also include the updating methods of 1st sheet, as 2nd is only output.
Regards!

 

[hreyo25]

I tried creating a sample file. Any help will be appreciated

 

[Luke M]

How do we know that "Electronic" refers to "Power elec". I would suggest trying to keep the names the same. Since this will require a macro, I switched from xlsx to xlsm.

 

[SirJB7]

Hi, hreyo25!

Give a look at the attached file. I made this changes:
a) Shrinked data validation list in column H deleting "Select one"
b) Added an adjacent column with the names of each related table in worksheet Output
c) Created a dynamic named range for columns H:I
TypeTable: =DESREF(Input!$H$4;;;CONTARA(Input!$H:$H)-1;2) -----> in english: =OFFSET(Input!$H$4,,,COUNTA(Input!$H:$H)-1,2)
d) Changed your ValidCh name definition to:
=DESREF(TypeTable;;;;1) -----> in english: =OFFSET(TypeTable,,,,1)
e) In worksheet Output, created a dynamic named range for each table, adding a last line on it 1st column with the constant "End":
PowerElectronicTable: =DESREF(Output!$A$7;;;COINCIDIR("End";Output!$A$7:$A$1048576;0)) -----> in english: =OFFSET(Output!$A$7,,,MATCH("End",Output!$A$7:$A$1048576,0))
PowerPneumaticTable: =DESREF(Output!$A$13;;;COINCIDIR("End";Output!$A$13:$A$1048576;0)) -----> in english: =OFFSET(Output!$A$13,,,MATCH("End",Output!$A$13:$A$1048576,0))
f) In the class module of worksheet Input, added code for the change event to trap modifications to cell A4, ValidChoice:

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
    ' constants
    Const ksSourceValue = "ValidChoice"
    Const ksSourceReference = "TypeTable"
    Const ksWSTarget = "Output"
    ' declarations
    Dim I As Integer, bHide As Boolean, sName As String
    ' start
    If Application.Intersect(Target, Range(ksSourceValue)) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    ' process
    With Range(ksSourceReference)
        For I = 1 To .Rows.Count
            If .Cells(I, 1).Value = Range(ksSourceValue).Value Then Exit For
        Next I
        bHide = (I <= .Rows.Count)
        If bHide Then sName = .Cells(I, 2).Value Else sName = ""
    End With
    With Worksheets(ksWSTarget)
        .Rows.Hidden = False
        If bHide Then
            .Range(sName).Rows.EntireRow.Hidden = True
        End If
    End With
    ' end
    Application.ScreenUpdating = True
    Beep
End Sub

Just advise if any issue.

Regards!

EDITED

PS: Didn't see Luke, who entered thru the window while I was eating my sandwich, Carlsberg wet, of course.