How to copy the used range from one Excel workbook to another?

24 Jun 20263 minutes to read

The UsedRange of IWorksheet returns the contiguous range that contains data in a worksheet. By default, Excel also considers cells that have only formatting applied as part of the used range.

The following code examples show how to copy the used range from a source workbook to a destination workbook in C# (cross-platform and Windows-specific) and VB.NET.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook sourceWorkbook = application.Workbooks.Open(Path.GetFullPath(@"Data/Source.xlsx"));
    IWorkbook destinationWorkbook = application.Workbooks.Open(Path.GetFullPath(@"Data/Destination.xlsx"));

    IWorksheet sourceWorksheet = sourceWorkbook.Worksheets["Sheet1"];
    IWorksheet destinationWorksheet = destinationWorkbook.Worksheets["Sheet1"];

    //Get the actual used range from source sheet
    IRange sourceRange = sourceWorksheet.UsedRange;

    //Copy the entire used range from source sheet to destination sheet
    sourceRange.CopyTo(destinationWorksheet.Range[sourceRange.Row, sourceRange.Column]);

    //Save the destination workbook
    destinationWorkbook.SaveAs(Path.GetFullPath(@"Output/Output.xlsx"));
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook sourceWorkbook = application.Workbooks.Open("Source.xlsx");
    IWorkbook destinationWorkbook = application.Workbooks.Open("Destination.xlsx");

    IWorksheet sourceWorksheet = sourceWorkbook.Worksheets["Sheet1"];
    IWorksheet destinationWorksheet = destinationWorkbook.Worksheets["Sheet1"];

    //Get the actual used range from source sheet
    IRange sourceRange = sourceWorksheet.UsedRange;

    //Copy the entire used range from source sheet to destination sheet
    sourceRange.CopyTo(destinationWorksheet.Range[sourceRange.Row, sourceRange.Column]);

    //Save the destination workbook
    destinationWorkbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim sourceWorkbook As IWorkbook = application.Workbooks.Open("Source.xlsx")
    Dim destinationWorkbook As IWorkbook = application.Workbooks.Open("Destination.xlsx")

    Dim sourceWorksheet As IWorksheet = sourceWorkbook.Worksheets("Sheet1")
    Dim destinationWorksheet As IWorksheet = destinationWorkbook.Worksheets("Sheet1")

    'Get the actual used range from source sheet
    Dim sourceRange As IRange = sourceWorksheet.UsedRange

    'Copy the entire used range from source sheet to destination sheet
    sourceRange.CopyTo(destinationWorksheet.Range(sourceRange.Row, sourceRange.Column))

    'Save the destination workbook
    destinationWorkbook.SaveAs("Output.xlsx")
End Using

A complete working example to copy the used range from a source workbook to a destination workbook using C# is present on this GitHub page.