none
Mneumonic Loop Performance RRS feed

  • Question

  • Hello,

    I am wondering if there is any way to improve the performance of copying a row of mnemonic values to a random large number of multiple rows below it based on input data.  For example, there is an Excel form with 10 mnemonic values in row 1 that needs to be copied to 100 rows below it.  Currently the range of mnemonics is being copied to an unused area of the spreadsheet which is used to paste each row below it one at a time.  The performance is not very good because of writing each row individually rather than storing all of the data in an array and writing the whole range containing all of the rows at once.

    The problem is that depending on the data, some of the rows may have a random number of secondary data associated with them (the same 10 mnemonic values).  There are two loops, one to get the primary data, and inner loop to get any associated secondary data that row may have.  Whatever is returned is copied to that unused area of the spreadsheet and pasted below the rows that have already been added.

    So I guess my question is:  Is there any type of custom structure that could be used to write all of the data to memory first allowing it to only be pasted to the spreadsheet once to improve performance.  A standard Array won't work because each slot may have secondary data associated with it.

    Wednesday, May 13, 2015 2:03 PM

Answers

  • Re:  copying data
    I should have asked where the secondary data is located relative to the primary data.

    But maybe this example code line will help...
      Rows("101:200").Value = Rows(3).Value

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Wednesday, May 13, 2015 6:09 PM

All replies

  • Re:  rows with secondary data

    You need to explain or show what "secondary data" is.

    '---
    Jim Cone
    Portland, Oregon USA
    Wednesday, May 13, 2015 4:56 PM
  • The primary data contains one row of values, and the secondary data (if it exists) contains the same row of values.  Each row of primary data may have 0 to many rows of secondary data associated with it.
    Wednesday, May 13, 2015 5:20 PM
  • Re:  copying data
    I should have asked where the secondary data is located relative to the primary data.

    But maybe this example code line will help...
      Rows("101:200").Value = Rows(3).Value

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Wednesday, May 13, 2015 6:09 PM