Combining Multiple Range Values into a Single Array

Hey,

Say I have several ranges (A1:A3,A5:A10,A13:A20) with different values in each cell. The values will change on a weekly basis. I want to store these range values into a single array, so I can perform different tasks on the array. How would I go about doing this?

Thanks.

Hello fervorking,

This UDF will take a range and convert it into a 1-D zero based array.

Function MakeSingleArray(ByRef RngAreas As Range)

  Dim Cell As Range
  Dim Data As Variant
  Dim I As Long
  Dim Rng As Range
  
    ReDim Data(RngAreas.Cells.Count - 1)
    
      For Each Rng In RngAreas.Areas
        For Each Cell In Rng
          Data(I) = Cell.Value
          I = I + 1
        Next Cell
      Next Rng
   
    MakeSingleArray = Data
   
End Function

Example of Using the Macro
This will convert the range shown into a single dimension array.

  Dim MyArray As Variant

    MyArray = MakeSingleArray(Range("A1:A3,A5:A10,A13:A20"))