Reference Named Ranges listed within a Cell

Question

Hello all brand new to coding here:

I am working on trying to set up views for a specific excel worksheet that I have. The worksheet has approximately 300 columns of data that is very cumbersome to navigate through. I have defined a named range for each column and have created some formulas to determine the list of named ranges on specific views eg. "namedrange_1, named range_2" etc.

I've entered some named ranges into the VBA code but noticed around 12 or so listings I get an 1004 Range reference error.

If I split the code into 3 rows and it appears to work. However I would like the code to be automated to what the formulated cell (listing of the named ranges for a specific view).

For example a formulated cell in another sheet currently appears as: "Namedrange_1, NamedRange_2, Etc"

Private Sub CMB_TAKEOFF_BASIC_Click()
    Application.ScreenUpdating = False

    Sheets("Pipe").Visible = True
    Worksheets("Pipe").Activate

    Call CMB_All_Click

    Columns("B:XFD").Select
    Selection.EntireColumn.Hidden = True

    Range("NamedRange_1,NamedRange_2).EntireColumn.Hidden = False
    Range("NamedRange_13,NamedRange_14).EntireColumn.Hidden = False

    ActiveWindow.ScrollColumn = 1

    Application.ScreenUpdating = True
End Sub

Looking for code to reference the named ranges list from calculation within an excel worksheet so I don't have to manually change the code every time a view is modified.

dimitris dimitris · Accepted Answer · 2019-09-16T07:13:32

This is the complete code with some corrections. This works fine I tested it. Please give it a try.

Private Sub CMB_TAKEOFF_BASIC_Click()
Dim i As Integer
Dim arr() As String
    Application.ScreenUpdating = False
    Worksheets("Pipe").Visible = True
    Worksheets("Pipe").Activate
    'Call CMB_All_Click
    Columns("B:XFD").EntireColumn.Hidden = True
'fill the array with values: split B20's value by commas.
    arr = Split(Worksheets("VIEWS").Range("B20"), ",")
'for each value (named range) hide its column.
    For i = 0 To UBound(arr)
        Range(arr(i)).EntireColumn.Hidden = False
    Next
    ActiveWindow.ScrollColumn = 1
    Application.ScreenUpdating = True
End Sub

Reference Named Ranges listed within a Cell

1 Answers