Saving data via a dynamic range to a VBA Array

[DonEB]

I've attempted to find a solution on the internet but have failed in coming up with a simple way to pose the question. So here is my problem with a little more detail. I have a range of data that can be found in Sheets("CommonData2").Range("E6:AH6"). However, the actual range of data can be anywhere from in just 5 columns to as many as in all 30 columns.

To determine the actual range size, I use the following: ColP = Sheets("CommonData2").Cells(6, 34).End(xlToLeft).Column

I resize my array using the newly found information which tells me which column the row ends.

Now, here's the problem I'm trying to solve. I'm having difficulty in coming up with a solution that would allow me to populate the array with the information contained within the range of E6:AH6. As previously stated, this range could be anywhere from E6:I6 to E6:AH6.

ColP = Sheets("CommonData2").Cells(6, 34).End(xlToLeft).Column
ReDim arr_CommonD2(1 To ColP - 4)

Normally, I would simply save a known range to the array, as follows:
arr_CommonD2 = Sheets("CommonData2").Range("E6:I6") 'In this example, data only exists in the 5 cells indicated. However, it could range from 5 to 30 cells.

But, when the range is dynamic and the size is not known until I perform the last column check above, how do I then save the data in the range to the array?

My best guess is to initiate a loop but my searching has not come up with a solution and I'm at a loss as to where to go from here.
For i = 5 To ColP
arr_CommonD2 =
Next i

Thanks to anyone who might be able to provide a little guidance.

Don

 

[bebo021999]

First, declare arr_CommonD2 as variant, not fixed array
Second, to use Range(Cell1, Cell2) to get a range from Cell 1 to Cell2
also using columns.Count to get the farest column index (XFZ), not harcoded "34"
Like this

Dim ColP&, arr_CommonD2 As Variant
With Sheets("CommonData2")
    ColP = .Cells(6, Columns.Count).End(xlToLeft).Column
    arr_CommonD2 = .Range("E6", Cells(6, ColP)).Value
End With

 

[DonEB]

Two thumbs up!! Works great... thank you!!