Document Solutions for Excel, .NET Edition | Document Solutions
Features / Worksheet / Range Operations / Work with Used Range
In This Topic
    Work with Used Range
    In This Topic

    Used Range is a bounding rectangle of used cells that returns the IRange object of used range on the specified worksheet.

    DsExcel provides users with an option to work with the already used range of cells in a worksheet in the following two ways:

    Work with worksheet's used range

    To work with worksheet's used range, you need to first get the used range by using the UsedRange property of the IWorksheet interface. After you accomplish this, you can customize the used range using the properties of the IRange interface.

    Refer to the following example code in order to get used range and customize it.

    C#
    Copy Code
    worksheet.Range["H6:M7"].Value = 1;
    worksheet.Range["J9:J10"].Merge();
    
    //Used Range is "H6:M10" 
    var usedrange = worksheet.UsedRange;
                
    //Customize the used range
    usedrange.HorizontalAlignment = HorizontalAlignment.Center;

    Work with feature related used range

    To work with feature related used range, you need to first get the feature related used range by using the GetUsedRange method of the IWorksheet interface. After you accomplish this, you can customize the feature related used range using the properties of the IRange interface.

    Refer to the following example code to get feature related used range and customize it.

    C#
    Copy Code
    IComment commentA1 = worksheet.Range["A1"].AddComment("Range A1's comment.");
    IComment commentA2 = worksheet.Range["A2"].AddComment("Range A2's comment.");
    
    //Comment used range is "A1:D5", contains comment shape plot area
    IRange commentUsedRange = worksheet.GetUsedRange(UsedRangeType.Comment);
                
    //Customize feature related used range
    commentUsedRange.Interior.Color = Color.LightYellow;

    Work with used range in selected range

    To work with used range in a selected range, you can use UsedRange property and GetUsedRange method of the IRange interface. Once fetched, you can customize the used range using the properties of the IRange interface.

    Note: In case of non-continuous selected range, the GetUsedRange method returns used range of the first range.
    C#
    Copy Code
    worksheet.Range["B5"].Value = "Google";
    worksheet.Range["D9"].Value = "Google";
    worksheet.Range["F7"].Value = "Google";
    
    // The used range1 is B5:D9
    IRange usedRange1 = workbook.Worksheets[0].Range["B3:E10"].GetUsedRange(UsedRangeType.Data);
    usedRange1.Interior.Color = Color.Blue;
    
    // The used range2 is F7
    IRange usedRange2 = workbook.Worksheets[0].Range["E1:F8"].UsedRange;
    usedRange2.Interior.Color = Color.Red;

    After you get the used range of cells using any of the above methods, you can customize the fetched range. For instance, you can set the row height and column width; tweak the row hidden and column hidden settings; perform certain useful operations like group and merge; add value, formula and comment to the used range in your worksheet.

    To view the code in action, see Get Used Range demo.

    See Also