Alternative method needed to transfer data columns quickly between ranges

nigelandrewfoster

Well-known Member
Joined
May 27, 2009
Messages
747
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:

Code:
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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?
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top