Populate an array without Iteration

[Joseph.Marro]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have searched everywhere and I am not sure if I can even accomplish this task. I know you can populate range values into an array without iteration. However, is it possible to convert a range into an array of row numbers without iteration? <o></o>
<o></o>
Example:

Sub Example()
Dim myRange As Range
Dim myArray() As Variant
Set myRange = Range("A23:A31")
myArray = myRange
Set myRange = Nothing
End Sub

<o></o>
How do I Modify this to produce an array containing the row numbers {23,24,25,26,27,28,29,30,31}? Is it even possible without iteration?<o></o>
<o></o>
Thank you,<o></o>
<o></o>
Joseph Marro<o></o>
<o></o>

 

[Comfy]

I don't think it is.

 

[Norie]

You could try this.

Dim rng As Range
Dim x
Set rng = Range("A12:A21")

x = Evaluate("ROW(" & rng.Address & ")")
 
x = Application.Transpose(x)

Though I have to ask why you want/need an array with the row numbers of a range?

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG20May18
[COLOR="Navy"]Dim[/COLOR] Ray
MsgBox Join(Application.Transpose(Evaluate("row(" & Range("A23:A31").Address & ")")))
'[COLOR="Green"][B]Hold data in Array[/B][/COLOR]
Ray = Application.Transpose(Evaluate("row(" & Range("A23:A31").Address & ")"))
MsgBox Join(Ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Joseph.Marro]

Norie,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thank you for your solution, it accomplishes the task. I had not thought to use Evaluate and Row() to return the array. What I am trying to accomplish is a custom match function using some fuzzy logic. I know I could use a For Each Cell In Range loop to accomplish the task, but it is horrifically slow. I was thinking it would be faster to stuff the entire range into an array and loop through it using a For Next Loop. The issue I was having is how to preserve the row numbers to be returned later. Am I attacking this problem from the wrong angle? Any suggestions would be greatly welcomed<o></o>
<o></o>
Thank you,<o></o>
<o></o>
Joseph Marro<o></o>
<o></o>

 

[Norie]

Joseph

Are you sure it's the For Each that's slowing things down?

If you do just want the row numbers for a loop couldn't you use something like this?

For intRow = Range("A21").Row To Range("A34").Row

Note, that would only work if the range is contiguous, which I think it might not be.

 

[MrKowz]

Joseph

Are you sure it's the For Each that's slowing things down?

If you do just want the row numbers for a loop couldn't you use something like this?

For intRow = Range("A21").Row To Range("A34").Row

Note, that would only work if the range is contiguous, which I think it might not be.

I remember reading somewhere that a For Each loop is actually faster than a For [var] x to y loop when dealing with a 1-dimensional array. I'll try to dig up that info again.

Edit: It was on Chip Pearson's site - http://www.cpearson.com/excel/optimize.htm

 

[xenou]

I can do it so fast I don't even need an array at all:

For i = 23 to 34: Next

 

[Joseph.Marro]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Maybe I am incorrect but I was under the impression that a for loop on an array is significantly faster than a for each loop on a range. I believe this is true for most collections. The only issue I see is how to efficiently populate the array.<o></o>
<o></o>
Thank you,<o></o>
<o></o>
Joseph Marro<o></o>
<o> </o>

 

[Norie]

Joseph

Didn't you say your For Each was 'horrificly slow'?

Perhaps it is the code within the loop which is slow.

We've not seen that but if you are using ranges there it could be.

Really depends what it's doing I suppose.