New to arrays-How do I copy an entire row from one array to another?

[mc84excel]

Hello all,

Is it possible to copy an entire row from one 2D array to another 2D array? (They are both variant arrays and have the same number of columns).

If so, how do I do it?

(The below doesn't work. Where is the syntax error?)

Please Login or Register  to view this content.

[TMS]

Try:

Please Login or Register  to view this content.

Regards, TMS

[mc84excel]

Thanks Trevor. Appreciate the help. However it is still giving me a syntax error.

[JosephP]

you have to loop and copy an item at a time

[mc84excel]

you have to loop and copy an item at a time

That's what I was afraid of/trying to avoid. (Loop and copying is slower plus I find that doing so doesn't copy all the data e.g. some string values wont transfer across - they are both variant arrays)

[JosephP]

I find it hard to believe it's noticeably slower unless you have enormous arrays

you shouldn't lose any data either-how did you do it and what data loss occurred?

[mc84excel]

I find it hard to believe it's noticeably slower unless you have enormous arrays

you shouldn't lose any data either-how did you do it and what data loss occurred?

The input array is a 2D variant array (166 rows by 31 columns)(created from worksheet contents).

I am trying to clean the data by looking for a criteria match on one particular column and on a match - loop the contents to a 2nd variant array. (In other words, the input array is 166 rows, 31 columns, the output array is X rows (will vary depending on what criteria looked up on) by 31 columns).

The problem is that the output array doesn't pick up all the contents of the matched rows. For example, column 16 contains descriptions of items (up to 17 len). These appear in the input (variant) array and appear on the Locals window as Type Variant/String. But in the Output array, column 16 appears in the Locals window as Type Variant/Empty (needless to say the Value is Empty too )

I am providing the code I am using to build the 2nd array:

Please Login or Register  to view this content.

[mc84excel]

It's not just column 16 either.
Each row in Column 2 (B) contains strings of 2 letters followed by 1 number and that doesn't carry across.
Each row in Column 4 (D) contains 2 digit numerals and that doesn't carry across.
There's more...

[JosephP]

I don't know why you transpose the first array-that will slow your code. also your output array is one column larger than input since you use base 0 for that. more importantly you only process every second column-remove

Please Login or Register  to view this content.

[Norie]

You could try using (Application.) Index/Match.

[mc84excel]

I don't know why you transpose the first array-that will slow your code. also your output array is one column larger than input since you use base 0 for that. more importantly you only process every second column-remove

Please Login or Register  to view this content.

Thanks JosephP

* Hmmm well I transposed the first array in order to measure the total rows & total columns (the example I gave was 166 rows by 31 columns but I have other ranges I need to process). (Otherwise UBound & LBound can only measure the first element, I want to measure both)
* Yes I noticed after I posted that it is 1 too many.
* Not sure about the 2nd column business. I am using the 2nd column to match data on. Although the output array is dropping data, it is definitely outputting more than just the second column. Still I'll give it a try and let you know.

[mc84excel]

more importantly you only process every second column-remove

Please Login or Register  to view this content.

Bingo! You are right again. (That line was causing it to skip all the even column numbers. What was I thinking?!)

[mc84excel]

Apparently not possible to copy arrays between arrays. Marking as solved.

[JosephP]

(Otherwise UBound & LBound can only measure the first element, I want to measure both)

no-use lbound(array, 2) for example to check the boundary of the second dimension ;-)

[mc84excel]

A related aside to this thread: I am posting the finished function in case anyone has a use for it. Any comments on the code (good or bad) are appreciated.

Please Login or Register  to view this content.

[Norie]

Did you try Index?

Here's a small example with a hard-coded array for the rows for the new array.

Please Login or Register  to view this content.

[mc84excel]

Did you try Index?

Here's a small example with a hard-coded array for the rows for the new array.

Hi Norie, I am looking at it now. It appears to build the new array in Locals ok but I am experiencing trouble getting it to write. (Subscript out of range. What is the issue with UBound(arrB, 2) ?)

Please Login or Register  to view this content.