Concatenate Function getting Type mismatch error

I am trying to concatenate columns with the following code (I know there are many ways to do this but am trying to follow through on this idea) but it is not working, I get "Type mismatch" here:

Please Login or Register  to view this content.

Thanks

Please Login or Register  to view this content.

Please Login or Register  to view this content.

One element within sTemp(x) is a string. The .Transpose function returns an array. Thus a type mismatch error. The fix would be to declare sTemp as a variant. Then each sTemp element is a subarray. Or leave sTemp as a string and join the transposed array like this..

sTemp(x) = Join(.Transpose(.Transpose(Intersect(UNR, UNR(i, 1).EntireRow).Value2)), ";")

There are a few other issues with the code which you'll see when you make the above fix. A major limitation will be that the Transpose function doesn't transpose noncontiguous ranges. So for your example, it transposes only columns A:B and excludes columns G and I. I would suggest you read the entire data columns A:I into an array and loop the array to concatenate the required columns row by row. This may seem like brute force, but array processing is fast.

This only works for columns A:B because of the contiguous range limitation.

Please Login or Register  to view this content.