Results 1 to 2 of 2

Thread: Alternative method needed to transfer data columns quickly between ranges
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2009
    Location
    Wythenshawe Manchester
    Posts
    728
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Alternative method needed to transfer data columns quickly between ranges

    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))
        rngTo.Offset(1).Resize(UBound(TempColumn) - LBound(TempColumn) + 1) = Application.Transpose(TempColumn)
         
    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 2010 and 2016 for Windows, 2011 and 2016 for Mac (alas) - God is good. ALL THE TIME!

  2. #2
    Board Regular
    Join Date
    May 2009
    Location
    Wythenshawe Manchester
    Posts
    728
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Alternative method needed to transfer data columns quickly between ranges

    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?
    Excel 2010 and 2016 for Windows, 2011 and 2016 for Mac (alas) - God is good. ALL THE TIME!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •