Can I range be copied to an array?

[AlexanderBB]

Obviously with a loop, but is there a better "Excel way" ?

I have
Set cr = .Range("Y" & Index & ":CP" & Index)
But I need to limit this to the last used cell letter before CP.

At the moment I'm doing this

 x = 96
        Do
            x = x = 1
        Loop Until .Cells(Index, x) = ""

Then I want this in a one-based 1D array.
Or should I just stick to a couple of loops ?

 

[jkpieterse]

Something like this:

Set cr = .Range(.Range("Y" & Index), .Range("CQ" & Index).End(xlToLeft))

 

[AlexanderBB]

Thanks that works great
PS I had 2 typos in my Do Loop but don't see how to edit it. But don't needs it now!

 

[jkpieterse]

Note that that code might cause issues when there are no empty cells in the range in question!

 

[AlexanderBB]

I can't make it fail with or without spaces.
BTW is there a better to get the columns?
Debug.Print Split(cr.Address, "$")(1)
Debug.Print Split(cr.Address, "$")(3)