Create an array based on cell values

[deadlyliquidx]

Hello friends!

I need to build an array based on a list in range A1:a300 on a sheet for a big code.

 sc.VisibleSlicerItemsList = Array(ActiveSheet.Range("a1"), ActiveSheet.Range("a2:a2"), ActiveSheet.Range("a3:a3"))

As you can see, I will manually have to put in range a1,a2,a3,a4 all the way up to 300 to build this array.
is there anyway this array can go through the column and build itself, like
sc.VisibleSlicerItemsList = Array(ActiveSheet.Range("a1:a300") which doesn't work when I tried it.
Better yet, would be if you can ask the array to stop building when there is a blank or error.

seems like a simple request but I cant figure it out for the life of me.
Let me know if you guys have any ideas.

Thanks!!!

 

[AlphaFrog]

This stops at the first error or blank cell.

    [COLOR=darkblue]Dim[/COLOR] arr1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], arr2 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], j [COLOR=darkblue]As[/COLOR] Long
    
    arr1 = Range("A1:A300").Value
    [COLOR=darkblue]ReDim[/COLOR] arr2(1 [COLOR=darkblue]To[/COLOR] 1)
    
    [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](arr1, 1)
        [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] IsError(arr1(i, 1)) [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]If[/COLOR] arr1(i, 1) <> "" [COLOR=darkblue]Then[/COLOR]
                j = j + 1
                [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] arr2(1 [COLOR=darkblue]To[/COLOR] j)
                arr2(j) = arr1(i, 1)
            Else: [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        Else: [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i
    
    [COLOR=darkblue]If[/COLOR] j > 0 [COLOR=darkblue]Then[/COLOR] sc.VisibleSlicerItemsList = arr2