+ Reply to Thread
Results 1 to 7 of 7

Quickest way to paste delimited string to sheet

  1. #1
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Quickest way to paste delimited string to sheet

    Hi all,

    I'm reading a CSV file from a shared folder into a string, which I then split on vblf (chr(10)), simply by

    Please Login or Register  to view this content.
    The resulting array has 69,118 rows, with each 15 columns of data. Going into the locals window, I can see that all elements of my 1D array have data (up until the last one).
    Now I want to paste this into Sheet1 by saying:
    Please Login or Register  to view this content.
    Resulting output is that I get 3583 rows with data, and 65535 rows with #N/A
    I SUSPECT it is due to "If you try and transpose an array that contains more than 65535 (2^16) elements then you receive a type mismatch", as detailed here: https://bettersolutions.com/vba/arrays/transposing.htm

    Pasting it to the sheet line by line is really slow, and writing to drive and then opening the file as csv, subsequently copying/pasting the range defeats the purpose.
    So, what's the quickest way to get the delimited string onto my sheet...?


    Any help is appreciated.

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Quickest way to paste delimited string to sheet

    1. What is your maximum number of columns? Mine is 16384.

    Please Login or Register  to view this content.
    2. Given your answer to Q1. What are you trying to achieve?
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Quickest way to paste delimited string to sheet

    Hi Mehtcik, thanks for replying.

    I don't see how 1. has anything to do with it, as it's a 1D array, thus 1 column.
    Compare the following two examples:

    Please Login or Register  to view this content.
    This works and gives me 65534 rows of random values.

    Now try this:

    Please Login or Register  to view this content.
    This gives me *4* rows of random values and the rest #N/A.

    2). As stated in the original question, I'm trying to find the fastest way to get that 1D array, holding more than 65K rows, onto my sheet.

    I could loop and do it row by row, but sincerely doubt that's the fastest way, so hoping for guidance on another, faster way.
    Thanks!

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,763

    Re: Quickest way to paste delimited string to sheet

    How about
    Please Login or Register  to view this content.

  5. #5
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Quickest way to paste delimited string to sheet

    Hi Fluff, thanks for the reply! I tried this, but it literally takes forever. It seems that each loop is triggering my (volatile) UDFs, despite 'application.enableevents = false'; will try to unvolatile those if this is the best way.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,763

    Re: Quickest way to paste delimited string to sheet

    The loop should not be triggering anything on the worksheet, as it's looping through an array in memory.
    If it's from pasting the data to the sheet, you will need to set calculation to manual, to prevent your UDFs from running.

  7. #7
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    3,910

    Re: Quickest way to paste delimited string to sheet

    Hi there,

    Looking at your simple example, does transposing it in two stages help?

    Please Login or Register  to view this content.

    Regards,

    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. most efficient and quickest way to populate data from sheet to array
    By twozedz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-21-2016, 08:42 AM
  2. Replies: 1
    Last Post: 09-16-2014, 10:02 AM
  3. Replies: 1
    Last Post: 03-24-2013, 05:57 PM
  4. Quickest way to get code onto every sheet?
    By kirsty in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-17-2010, 08:50 PM
  5. Insert delimited string
    By nag_ec@sify.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-20-2008, 05:03 AM
  6. Paste comma delimited text into a spread sheet
    By Dave Plyer in forum Excel General
    Replies: 5
    Last Post: 06-21-2005, 09:05 AM
  7. [SOLVED] Quickest way to copy then Paste Special-Values in place?
    By Hash@example.org in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-18-2005, 10:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1