Alternative method needed to transfer data columns quickly between ranges

[nigelandrewfoster]

Hiya

Very quickly, I have gotten into the habit of passing data to and from arrays and then sending the arrays directly to workbook ranges. This has sped up my code considerably. When I have to transfer a series of non-contiguous columns the speed increase is still considerable, but I have been using Application.Transpose to ensure the one-dimension array is correctly oriented, thus:

Public Sub Transfer_Data_Column(rngFrom As Range, blnFromIgnoreHeader As Boolean, rngTo As Range, blnToIgnoreHeader As Boolean)
    Dim TempColumn() As Variant
    
    TempColumn = Application.Transpose(Whole_Column(rngFrom, True))
[B]    rngTo.Offset(1).Resize(UBound(TempColumn) - LBound(TempColumn) + 1) = Application.Transpose(TempColumn)[/B]
     
End Sub

The problem is that it will not work on a Mac. I am getting a Type mismatch error on the highlighted line for a particular column of data containing up to 627 characters of text (I can see no other difference to the other columns it succeeds with). It works fine on my PC but not the Mac. I'm not trying to drag anyone into the murky world of writing code in VBA for Mac - I wouldn't be so cruel, but are there alternative solutions I might try???

Thanks in advance!

Your humble servant Nigel

 

[nigelandrewfoster]

It's ok, I think I misunderstood a previous post. I removed Application.Transpose in both cases and it works on both computers now. Can someone clear up the point of Application.Transpose? It's something I have gotten use to using because otherwise when you are checking values in the Watches window you are faced with 100,000 individual boxes to click to check values. Is there any other value to using it?