Using Array Copy and Insert Row Based On Cell Value

  • I have an Excel sheet (Review) which has individual rows of data by a unique "EID" number. I need to copy and insert row(s) if the cell value under plan column (D) is “TRS Medical 2015”. After the row(s) that meet criteria are copied/inserted into the Review sheet there are a few other data modifications, e.g. replace employer cost with "0" zero, change deduction code based on payer product and highlight the copied/inserted row(s).

    The amount of data which needs to be evaluated may have 10K or more individual rows by unique EID number. I used an array to process the data because, as indicated in some forums, it’s more efficient and faster to use an array instead of looping through cells in the Excel sheet. I found VBA for array and modified it to do what is indicated above.

    When the data is processed for a small number of records, the VBA does what it’s supposed to. However, when I tried processing 6,000 records, it runs a long time. I don’t know how long it actually runs with the 6000 records because I had to break its execution after 20 minutes.

    So I’m hoping someone can look at my sample Excel file and its VBA to see if why it takes too long or if there is a better way to do this.

    Attached is my sample Excel file. It has two sheets - one is named “Review Original Data” which is the data before executing the VBA. And the other one is “Review” which has results after executing the VBA.

    Thank you!

  • Re: Using Array Copy and Insert Row Based On Cell Value

    You can try this.
    I copied your data down to 20,000+ row and it took about 5 seconds to run.

  • Re: Using Array Copy and Insert Row Based On Cell Value

    skywriter, I’m grateful for your code and it does what I need to do. But I came across a slight issue after testing it a bit more and I can’t seem to figure out how to fix it. Can you please have a look at it?

    The issue seems to happen when the data source has two adjacent records where the plan name is “TRS Medical 2015” but for different EIDs. After the VBA runs, for the first EID record having plan name “TRS Medical 2015” it will generate three extra rows in addition to the copied row highlighted in orange. And it deletes the other EID record having plan name is “TRS Medical 2015” instead of creating its copy row highlighted in orange.

    I have attached the tested Excel file with the two sheets as before. But in the sheet named Review Original Data I placed one row with plan name “TRS Medical 2015” right above another one having plan name as “TRS Medical 2015”. The Review sheet has the results showing described issue.

    Really appreciate it!