Loading an array with a varying range size

[krice1974]

Hello all. Array question here. I'm new to arrays, and I want to load one with data from a worksheet query table that will vary in size each time it's executed. The length will vary, but the range is continuous, and five columns wide. I'd prefer a do while loop, looping until a blank cell is encountered. Can someone get me started with general syntax and setup of the array?

Many thanks in advance...

 

[RoryA]

The Querytable has a ResultRange property - you can simply assign the value of that to a Variant to get an array (1-based).

 

[Richard Schollar]

Hi

The good news when it comes to loading arrays fromranges is that you don't need to loop and you don't need to dimension the array size before you fill it - VBA handles this automatically. All you need to do is determine the range of data first:

Dim myArray As Variant
 
myArray = Worksheets(1).QueryTables(1).ResultRange.Value

 

[krice1974]

That's a big help!! Then how do I determine the query table? I set it up through data/get external data/ from web

 

[Richard Schollar]

If it's the only qt on your sheet it will be index 1 in the QueryTables collection (ie you can use the code I gave).

 

[krice1974]

OK, this is very helpful and could be a breakthrough of sorts on a stock market data app I've been struggling with. I'll work on it. Thanks.

 

[krice1974]

OK, didn't work. The query table itself is quite non- continuous, with headers, blank lines etc. The continuous part I mentioned originally is nested within the web query. So any help from here? So close yet so far...

 

[krice1974]

Maybe my original idea to loop through and copy to array is worth pursuing after all? I'm open to any suggestions...

 

[RoryA]

Loop through one of the columns, incrementing a counter variable by 1 each time, until you get to an empty cell. You can then assign the relevant sized range to a variant as before.

 

[Richard Schollar]

What do you want to do with the array data ultimately? I don't fully understand the layout of the query table data either - could you post a screenshot (eg by using a html maker like Excel jeanie - see link in my sig)?