May be making this too complicated...

[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!

 

[BigC]

1. In Excel (not VBA), assign a unique Defined Name to each range listed (select entire rows in the RefersTo field of the Name box)
2. In the VBA procedure/code (which is not a "formula"!) replace the cell references with the Defined Name enclosed in inverted commas [e.g. Range("HideRng1").EntireRow.Hidden = True]

Now whenever you insert or delete rows in the sheet, the Defined Names will automatically adjust, and the VBA code will in turn refer to the correct ranges when it executes. (This is the best practice way to work with ranges in VBA >> never use cell references because they are more "absolute" than Excel absolute references when included in VBA code!)

If you do the above, your code should work without having to make any changes - to either the code or the Defined Names.

HTH

 

[Teeroy]

I agree with @BigC about using named ranges in your case. Because you are testing the same value multiple times you would be better to use a Select Case structure rather than 4 If statements. This is also easier to amend to add more cases. e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo handled
If Intersect(Target, Me.Range("C18")) Is Nothing Then Exit Sub
Application.EnableEvents = False 'to prevent endless loop
Range("Range15").EntireRow.Hidden = False
Select Case Range("$C$18").Value
Case "G3LabUniversalDV"
    Range("Range1").EntireRow.Hidden = True
    Range("Range2").EntireRow.Hidden = True
    Range("Range3").EntireRow.Hidden = True
    Range("Range4").EntireRow.Hidden = True

Case "G3LabUniversalPC"
    Range("Range5").EntireRow.Hidden = True
    Range("Range6").EntireRow.Hidden = True
    Range("Range7").EntireRow.Hidden = True

Case "G3ProUniversal"
    Range("Range8").EntireRow.Hidden = True
    Range("Range9").EntireRow.Hidden = True
    Range("Range10").EntireRow.Hidden = True
    Range("Range11").EntireRow.Hidden = True

Case "G3PC"
    Range("Range12").EntireRow.Hidden = True
    Range("Range13").EntireRow.Hidden = True
    Range("Range14").EntireRow.Hidden = True
End Select
handled:
Application.EnableEvents = True
End Sub

Also please use code Tags for VBA; it's much easier to follow. See Code Tags - MrExcel Message Board

 

[rguliuzza]

Thank you BigC - that is what I was looking for! I appreciate it.