Storing range references in an array

[Bill_the_Bear]

Hi all,

I'm having trouble finding a solution to this problem.

I have a worksheet into which I want to copy data from many different ranges on many other worksheets, the exact number of ranges depends on how many are found. What I need to be able to do is have my macro identify these ranges while looping through however many sheets are in existence (I can do that bit) and then "remember" the ranges it has identified (I can't do this bit).

Its important to "remember" the ranges rather than just copy each one as its found because some user input is required first:
a) Not everything found will be copied, only what the user selects
b) Its possible that the detection will be imperfect and so the user is going to be given the opportunity to manually select additional ranges

I assume the easy thing to do is have an array, and each time the code finds a range it saves a reference to that range into the array. I can then present this to the user on a userform and add flags based on their input to the array to indicate what to do with each found range.

The problem:

I can't find how to store references to ranges into an array (or into cells of a hidden sheet or any other substitute etc...). When I search for this problem the results are overwhelmed by questions about storing the values in a range into an array, and if there are any results about storing the reference to the range I can't see them amongst all these other results.

I suppose it wouldn't be too much less efficient to just copy in the values to the array instead, but it seems like this will mean the array is storing a vast quantity of data only for the user to then select only a small fraction of it to then be copied to the sheet. It seems to me much better to hold in the array the locations of the data and only start to copy them after the user has decided which ones they want. Also better to do it direct from one sheet to the other instead of having two copy tasks, first from the many sheets to the array then from the array to the master sheet.

I'm sure this must be quite simple, all I really need to know is how to put one range reference into an array and then I can work out the rest from there.

Thanks for any help you can offer!

 

[Norie]

Couldn't you just store the address of the ranges in the array?

Dim arrRanges()


For I = 1 To 10
    If Cells(I, "A").Value = "Thing" Then
        ReDim Preserve arrRanges(cnt)
        arrRanges(cnt) = Cells(I, "A").Resize(,10).Address
        cnt = cnt+1
    End If
Next I

If cnt>0 Then
    MsgBox Join(arrRanges, vbCrLf)
End If

 

[Bill_the_Bear]

Norie,

Thank you, I tried storing address in my array using some trivial example and it looks like that will do exactly what I need!

Its going to take me longer to implement this into the larger function, plus debug everything because I'm a noob, but I'm 99% sure this is going to work.

Thank you!