Append range to end of variant array

[macroCoder]

Hello,

I've hit a brick wall.

I want to append a range of values to the end of an array. I define the array as a variant -- Dim myArray() -- and then "move down" a spreadsheet using Excel's OFFSET function.

So far so good. But when I encounter a row of data that meets the criteria I'm searching for, I select the row and... hit a brick wall.

Because I don't know how to APPEND the selected range to the end of the array. I only seem able to replace the entire array with each newly selected range.

The data I'm searching for in the spreadsheet is scattered across discontinuous rows. It is not in a contagious range. So I can't just define my array based on a contiguous range -- I have to "build" the array one criteria-fitting row at a time.

(I could create a new sheet and paste all of the ranges I encounter so that sheet, forming a contagious range, which I could then pass to an array -- but this solution would run too slowly. There has to be a better way!)

Thank you for any suggestions/examples.
-- macroCoder

 

[Norie]

Why are you using an array?

Why do you think getting all the ranges that meet your criteria on to another worksheet would be slow?

As far as I can work out you are currently using a loop, have you considered a auto/advanced filter?

Depending on exactly what you are doing those could be applied with just a few lines of code and no looping.

PS If you really want to 'append' to an array then take a look at ReDim.

 

[macroCoder]

I can use a loop to cycle through a range, appending one value at time to the array, and that is what I've done for the time being. It doesn't even require ReDim if I define the array to be larger than needed.

But especially when working with large amounts of data, array operations are faster than reading and writing to a sheet (http://www.dailydoseofexcel.com/archives/2006/12/04/writing-to-a-range-using-vba/)

Is there a way to append a contiguous range to the end of a variant array?

Thanks for your response.
-- macroCoder

 

[Norie]

You could use a filter to get the values you want, put them somewhere and then assign them to an array - without any looping.

Why would you define the array larger than needed?

In the code you've posted all you seem to be doing is declaring the array - you aren't dimensioning it.

What exactly do you mean by adding a contiguous range at the end of an array?

Am I missing something here?

PS I know about the array vs range thing but I really don't think the performance difference is that great.

Unless you are dealing with gigantic datasets.

 

[macroCoder]

Yes, it's a large data set.

By defining the array larger than needed, there is no need to redimension it.

True, the code I posted in the original message showed no array dimensions. My reference in the second post to an array that I had defined to be larger than needed referred to code that I had written between posting the first and second messages.

By appending a contiguous range to the end of an array, I mean: How do I append Range("A1:A5") to the end of an array? This is the question.

We can call it a thought exercise for discussion's sake. Or take it on faith that I (a) have declared a variant array, or (b) will use ReDim to resize the array as elements are added to it, or (c) defined a larger array than I'll need, to avoid the need to redimension.

My focus is on appending Range("A1:A5") to an array -- assuming that array has been properly defined or resized to allow the addition of Range("A1:A5").

Thanks for your reply. I think we're getting close.

 

[Norie]

I still don't quite get what you mean by append a contiguous range to an array.

What exactly would you be appending to the array?

What would be the dimensions of the array?

It also a little unclear what range(s) you want to work with.

In your first post you seemed to want to do something with a row that satisfied some sort of criteria.

Now you are referring to the range A1:A5 - that's not a row.

Perhaps we're just getting terminology mixed up.

Posting some sample data and more information might clear things up.

I've got a few ideas in my mind but I think to post them might just muddy things up further.