The Excel connector has two methods that would accomplish this; ExportData and ImportData. You can call ExportData on the cells you wish to export which will return a DataTable. You can then pass this into the method ImportData and set the cells that you wish to move the data into.
Posted: 3 years ago
Updated: 3 years ago
Posted: 26 Jun 2017 2:16 EDT Updated: 26 Jun 2017 2:17 EDT
The best answer is that there would be some logic and calculation involved. The easiest way using the components available would be to iterate through each row using GetCellValue until you get to the end of your range. You'd record this value and use it as the second parameter on ExportData.
You can also do this in C# and that would likely be faster if you had a lot of rows (more than say 25). You would basically export the data from your start until some large number that would always exceed the amount of rows you would have. Then, you'd pass the DataTable into a C# script and remove any of the blank rows from it. Then you could pass this DataTable back into the destination worksheet.