Insert dynamic rows to excel during runtime - getting error

[Janu]

Hi all ! Hope you are doing good. I am trying to insert dynamic rows to excel. But sometimes i am getting error. 

I have a excel file with  two sheets. I have to filter for specific record from the Sheet1, check for duplicates and I have to paste in sheet2. The second sheet(Sheet2)  has 36 different sub tables with diff number of rows and column.  In each table I have to paste values( which I filtered from Sheet1).
In certain cases, during run time I have to insert additional rows to the tables in Sheet2. So i am using the action select and insert.

STEPS Followed:

1.Open instance.
2. Open Workbook
3. Get worksheet has collection (Sheet1) - Collection1
4. Filter for specific criteria and save it to a final Collection. So I have got 36 collections to paste it in 36 tables in Sheet2.
5. Get Worksheet has collection (sheet2)-Collection 2
6. Looping the collection 2 to get the row number of Table 1 & Table 2 so I have the counter values which specifies the exact starting row number for table 1 & table 2. with these counters i can get the row difference.  saving the row difference between table 1 & Table 2 in a data item (RowDiff)
7. Logic used: if collection1  rows are empty no need to write values, if collection1 has values then get number of rows to paste & check whether need to insert additional rows.
8. If collection2 has got less rows and if I want to insert rows i am actually getting the number of rows to add and trying to select the cell and using Insert action. For eg: The starting cell of table1 & 2 is 8 & 28. Row diff is 20, i need to write collection with 30 rows so i have to insert ten extra rows. I  am trying to select the number of rows to insert extra rows. using activate worksheet>>Select (Specifying cell refence as 27:37)>> Insert (tried using "Shift Up" & Entire Row">> write collection (specifying cell reference as A8" 

The challenge here is between table1 and table 2 there is a comment section row which is a merged row. So sometimes my insert option works and sometimes its not working and I am getting the below error 

Exception : Failed to delete selection: This selection isn't valid. Make sure the copy and paste areas don't overlap unless they are the same size and shape.
I have attached the screenshot of the excel which exactly looks like the one i am working. And also the screenshot of the stages. 

Counter 2 - Starting cell of Table1
Counter 3: Starting cell of Table 2.

thanks

[John__Carter]

It sounds like it's the merged cell that's the problem, and even if you tried to do the same operation manually you'd get the same error. Maybe you need to find the row number of the Summary and Comments cells first, so that you can avoid them in your subsequent operations.

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

[Janu]

Thanks John for your reply. But I don't experience the same issue when I try to insert rows  manually. The select and Insert option is not  working continuously.  Few tis when I run it worked and most of the times i am getting this error.

[John__Carter]

Hmm OK. The message 'Make sure the copy and paste areas don't overlap unless they are the same size and shape' comes from Excel not BP, so there must be a reason for the error. How about inserting some extra rows, to provide a margin? Eg if you have 10 additional rows of data, insert 12 empty rows into the sheet to guarantee you have enough room?

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------