Using Array Copy and Insert Row Based On Cell Value

xl4betrm · Apr 19th 2016

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!

skywriter · Apr 20th 2016

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.

Code

Sub test_Array_Copy_Row()
Dim rRange As Range, j As Range, k As Range
Application.ScreenUpdating = False
    With Sheets("Review Original Data").Range("A1").CurrentRegion
        .AutoFilter 4, "TRS Medical 2015"
        Set rRange = Intersect(.Columns("A:R"), .Parent.UsedRange).Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(12)
        .AutoFilter
        rRange.Offset(1, 0).EntireRow.Insert
        rRange.Offset(1, 0).Interior.ColorIndex = 40
            For Each j In rRange.Offset(1).Rows
                j.Value = j.Offset(-1).Value
                Set k = j.Cells
                    If k(5).Value = "TRS - ActiveCare 1-HD" Then
                        k(6).Value = "BAC4"
                    ElseIf k(5).Value = "TRS - Scott & White" Then
                        k(6).Value = "BSW"
                    End If
                k(10).Value = 0
                k(12).Offset(-1).Value = 0
            Next j
    End With
Application.ScreenUpdating = True
End Sub

Display More

xl4betrm · Apr 20th 2016

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

skywriter, your code is perfect for what I need. Thank you very much!

skywriter · Apr 20th 2016

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

My pleasure, thanks for the feedback. :cool:

xl4betrm · Apr 21st 2016

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!

Using Array Copy and Insert Row Based On Cell Value

Participate now!

Share