Need help using Cell contents for Range of Rows to be Hidden - Would INDIRECT work?

[rguliuzza]

I have a spreadsheet for creating a quote with a number of dropdown choices. Once the dropdown choice is made, a number of the rows are hidden by using the following formula:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Me.Range("C18")) Is Nothing Then Exit Sub
 Application.EnableEvents = False 'to prevent endless loop
 Rows("1:2000").EntireRow.Hidden = False
 If Range("$C$18").Value = "G3LabUniversalDV" Then
 Range("63:122").EntireRow.Hidden = True
 Range("195:396").EntireRow.Hidden = True
 Range("1249:1265").EntireRow.Hidden = True
 Range("1301:1302").EntireRow.Hidden = True
 End If
 If Range("$C$18").Value = "G3LabUniversalPC" Then
 Range("123:396").EntireRow.Hidden = True
 Range("1071:1248").EntireRow.Hidden = True
 Range("1296:1300").EntireRow.Hidden = True
 End If
 If Range("$C$18").Value = "G3ProUniversal" Then
 Range("63:194").EntireRow.Hidden = True
 Range("272:359").EntireRow.Hidden = True
 Range("1249:1265").EntireRow.Hidden = True
 Range("1301:1302").EntireRow.Hidden = True
 End If
 If Range("$C$18").Value = "G3PC" Then
 Range("63:271").EntireRow.Hidden = True
 Range("1071:1248").EntireRow.Hidden = True
 Range("1296:1300").EntireRow.Hidden = True
 End If
 Application.EnableEvents = True
 End Sub

What I am having a problem with is when I add a Row to the Spreadsheet, these sections are not automatically updated and I have to go back and enter in all the new ranges by hand. Is there an easier way to do this? In addition to this, am I able to use and formula name for the range and just change the formula when a row is entered? Please help!

[kelleytr]

Is there anything unique about the starting and ending rows for those ranges? If so you could have them dynamically created using a match function.

[rguliuzza]

Hi Kelley,

The rows are just products that we provide for use in our Quote tool and based on the drop-down, I want to hide the rows not needed to narrow the tool down a little. It is a little daunting to look through 1500 line items and although you can search for each one, this would save some time.

I am not familiar with the MATCH function in this case. As you can see by the code, there are arrays of rows to hide and when I add three products into the mix, it is not intuitive enough to expand the arrays.

Oh well, if you have an example of the MATCH function in light of this issue, I would welcome it.

Thank you for your response.

With best regards,
Rick

[natefarm]

I would suggest naming your ranges, and to make it easy, use VBA to do it. Copy and paste the following into a code module(replace Sheet1 with your sheet name):

Sub CreateNames()
    ActiveWorkbook.Names.Add Name:="G3LabUniversalDV", RefersToR1C1:="=Sheet1!R63:R122,Sheet1!R195:R396,Sheet1!R1249:R1265,Sheet1!R1301:R1302"
End Sub

Copy and paste the code line 3 times and adjust the copies for your other three ranges. Then run the code to create the names. Then replace all your code above with:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Target, Me.Range("C18")) Is Nothing Then Exit Sub
   Application.EnableEvents = False ' I don't know if this is still needed or not
   Rows("1:2000").EntireRow.Hidden = False
   Range(Range("C18").value).EntireRow.Hidden=true
end sub

I haven't tested this entirely, but it should be close.

[rguliuzza]

Hi Nate,

I appreciate the code and will try it out. Sometimes I need more eyes on the issue.

Thank you again,
Rick

[FDibbins]

Once the title is changed, I can recover the post I removed

edit: Thanks for ther title change

[rguliuzza]

Thank you!

[kelleytr]

rguliuzza,

Were you able find a solution using NateFarms suggestion? If not then I can spend some more time on it.

[rguliuzza]

Hello FDibbins,

I apologize for an incorrect post. I will update the title and it looks like you already edited the code?

Thank you,
Rick