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.