2D array for row & column data, how to redim to add rows?

Hi folks!

I've been banging my head on this challenge for several long days. I've read hundred of pages in book and forums. I can't seem to find an answer to this simple issue.

Scenario: My Excel 2010 VBA code has to loop through ~100 rows of Excel data on a worksheet. The xlsm is created by others--I have no control over the source creation. My macro reads one row at a time, and if it meets certain criteria, I will add its relevant cell values to an array for later processing. Once I've got my array populated, I will present the array in a UserForm ListBox. The number of *columns* in the worksheet is known, what I do NOT know is how many *rows* there will be. Typically would be between one and a hundred rows. Certainly never more than 200 rows.

I have spent days learning and coding up VBA arrays, and I have the gist of it figured out, and working. However, I can't seem to find out how to get the correct number of rows in my array, without extras! I've used dynamic, but found I can't use ReDim Preserve on the *first* dimension: rows.

Presently, I'm got code running using a static array, figuring I could allocate more of the first dimension (rows) than I need, then trim it back with ReDim after I'm done processing. [buzzer] Wrong!

The only "trick" I've read from Professor Google is to transpose the dimensions of the array in the ReDim command. But that makes no sense to me. I must be missing something obvious! Adding rows to a 2D array has got to be the most common task in ArrayLand.

Please help!!!

BigTimberMan

Thank you for the reply Leith. Sorry for the delay. I had to step away from this part of the project due to other demands. Meanwhile, working with arrays has become hazy in my mind. Hopefully the edited-down block of code makes some sense. I edited the code heavily to make it somewhat more concise.

I need a two-dimensional array that can be dynamically sized, as I don't know ahead of time how many rows I need to harvest and place into the array. The array should be similar to an worksheet, that is, it would have rows and columns. The array elements should look like this: A₁,₁ - A₁,₂ - A₁,₃ - Etc. Then, when my code finds another worksheet row to be put into the array, it would look like this: A₂,₁ - A₂,₂ - A₂,₃ - Etc.

I have read that it is only allowed that the LAST dimensioned be resized. That's just nuts! Based on suggestions I read in forums, I flip-flopped the dimensions, and had nothing but headaches with that and it did not end well!

Lastly, I understand that ReDim-ing arrays is slow. But as I said, speed is not a huge issue, and besides, we'll typically be dealing with a dozen or two dozen rows total.

Please Login or Register  to view this content.