I thought Append Range would append the data from the copied data to the first blank cell/row of the intended sheet. However, it is pasting the data around row 800 instead of row 40. What’s the secret to have the append range start after the last line of existing data in the pasting sheet?
Thanks
The trick might be excel “UsedRange”
“A used range includes any cell that has ever been used.”
So if your worksheet contained data that were later deleted the space is still considered as “occupied” and AppendRange activity will append below it.
You can check if it your case by openning your worksheet and pressing “Ctrl-End”. It point you to the UserRange end.
Cheers
Clean the excel file by deleting empty rows below your data before running the bot.
Bot considering the formatted rows below your data as rows which resulting in this issue.
Deleting the empty rows should fix this.
I tested my hyphotesis.
It is valid in case of using “Excel Application Scope” + “Append Range” → data are appended below “UsedRange”
In case of using “Append Range Workbook” data are appended right below the last “real” data, i.e. not leaving the whitespace.
Cheers
It did work if I started with a fresh worksheet, however in Studio X is there a function to clear out the worksheet everytime I run the bot.
You can use delete range activity for this
Makes sense. I was using clear sheet to clear the sheet before pasting…
Thanks for the help!
Attached workflow contains code that will shrink the “UsedRange”
ShrinkRange.xaml (12.2 KB)
Cheers
If sorted, kindly close the thread by marking my answer as solution so it will be helpful to other community members as well 
I do not know what clear sheet means, but “Delete Range” proposed by @ashokkarale will help partially → if the deleted range contains some empty rows those in some cases won’t be deleted. This can be avoided using options ShiftCells=ON and ShiftOption=EntireRow
What works definitely is “Insert/Delete Rows” which truly deletes rows including empty ones.
Cheers
I ended up deleting rows and it seemed to work.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.