Put rage of cells in an array

[nikko50]

Hi all!

So I need to build an array with values from a couple sets of cell ranges. I need to grab the values from row 2 Cell B - Cell E and row 2 Cell J - Cell AO. How can I do that?

 

[Domenic]

Assuming that the active sheet contains the data, the following macro fills an array with values from the two specified ranges, creates a new worksheet, and transfers the contents of the array to the new worksheet in Column A, starting at A2...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] test()

    [COLOR=darkblue]Dim[/COLOR] aData() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] rData [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rCell [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] Indx [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] rData = Union(Range("B2:E2"), Range("J2:AO2"))
    
    [COLOR=darkblue]ReDim[/COLOR] aData(1 [COLOR=darkblue]To[/COLOR] rData.Cells.Count, 1 [COLOR=darkblue]To[/COLOR] 1)
    
    Indx = 1
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rCell [COLOR=darkblue]In[/COLOR] rData.Cells
        aData(Indx, 1) = rCell.Value
        Indx = Indx + 1
    [COLOR=darkblue]Next[/COLOR] rCell
    
    Worksheets.Add before:=Sheets(1)
    
    Range("A2").Resize(UBound(aData)).Value = aData
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!