Error when referring to many multiple named ranges in Range property

Question

I have around 236 named ranges (columns) for a large table of data. I get this error when trying to split up the long code-line of delimited named ranges:

Run-time error '1004' Application-defined or object-defined error

E.g.:

Worksheets("Sheet1").Range("foo1,foo2" _
    & "foo3,foo4" _
    & "..." _
    & "foo235,foo236")

I am trying to filter and unfilter columns based on specific criteria (named ranges). Everything seems to work fine (for smaller strings that only span 1 line in length) until I have to split the code into multiple lines since it reaches the end of the window..

Code -

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$B$3" Then
        Worksheets("Sheet1").Range("Fruit," _
            & "Months,Colour").EntireColumn.Hidden = Target.Value = "CustomView"
    End If

    If Target.Address = "$B$3" Then
        Worksheets("Sheet1").Range("Colour,Number" _
            & "Months").EntireColumn.Hidden = Target.Value = "Custom2View"
    End If
End Sub

This code doesn't seem to work. I think it has something to do with the quotes and how excel reads it but i haven't been able to find a fix yet.

New code being tested based on suggestions in comments results in an error Run-time error '1004' Application-defined or object-defined error

Private Sub Worksheet_Change(ByVal Target As Range)

Dim arr, i As Long, rng As Range

If Target.Address = "$B$3" Then
    arr = Split("foo1,foo2,foo3,...,foo266,foo267", ",")
 Set rng = Worksheets("Database").Range(arr(0))
 For i = 1 To UBound(arr)
 Set rng = Application.Union(rng, Worksheets("Database").Range(arr(i)))
 Next i
 rng.EntireColumn.Hidden = (Target.Value = "CustomView")
End If

End Sub

Answer 1

You can use Application.Union to build up a range and then hide/show that range in one shot.

EDIT: based on your second shared file I think you need something like this. Your previous code was not checking the value of the "view name" cell and was applying all of the views, leaving you with the last one...

Eg:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim arr, q As Long, rng As Range, sht As Worksheet

    Set sht = Worksheets("Database")

    If Target.Address = "$B$3" Then

        'unhide all columns forst
        sht.UsedRange.EntireColumn.Hidden = False

        Select Case Target.Value
            Case "CustomView"
                arr = Split("A,B,C_,X,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM," & _
                  "AN,AO,AP,AQ,AR,AS,AT,AV,AW,AX,AY,AZ,BA,BB,BC,BD,BE,BF,BG," & _
                  "BH,BI,BJ,BK,BL,BM,BN,BO,BP,BQ,BR,BS,BT,BU,BV,BW,BX,BY,BZ,CA," & _
                  "CB,CC,CD,CE,CF,CG,CH,CI,CJ,CK,CL,CU,CV,CW,CX,CY,CZ,DA,DB,DC", ",")
            Case "XX100View"
                arr = Split("D,E,F,G,X,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO," & _
                   "AP,AQ,AR,AS,AT,AV,AW,AX,AY,AZ,BA,BB,BC,BD,BE,BF,BG,BH,BI,BJ," & _
                   "BK,BL,BM,BN,BO,BP,BQ,BR,BS,BT,BU,BV,BW,BX,BY,BZ,CA,CB,CC,CD,CE," & _
                   "CF,CG,CH,CI,CJ,CK,CL,CU,CV,CW,CX,CY,CZ,DA,DB,DC", ",")
            Case "OtherView"
                arr = Split("A,B,D,E,F,G,H,I,X,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM," & _
                    "AN,AO,AP,AQ,AR,AS,AT,AV,AW,AX,AY,AZ,BA,BB,BC,BD,BE,BF,BG,BH," & _
                    "BI,BJ,BK,BL,BM,BN,BO,BP,BQ,BR,BS,BT,BU,BV,BW,BX,BY,BZ,CA,CB," & _
                    "CC,CD,CE,CF,CG,CH,CI,CJ,CK,CL,CU,CV,CW,CX,CY,CZ,DA,DB,DC", ",")
        End Select

        If Not IsEmpty(arr) Then
            Set rng = sht.Range(arr(0))
            For q = 1 To UBound(arr)
                Set rng = Application.Union(rng, sht.Range(arr(q)))
            Next q
            rng.EntireColumn.Hidden = True '<<edited
        End If 'got a view

    End If 'is view name cell
End Sub

PS - your range names don't need to include all of your data: a single cell would be fine, since you use EntireColumn to expand it to the entire sheet height anyway.