Behavior of IRange.SpecialCells

Posted by: yyoshimura on 17 May 2021, 11:12 am EST

    • Post Options:
    • Link

    Posted 17 May 2021, 11:12 am EST


    We are using the IRange.SpecialCells(SpecialCellType.Constants) to get all constant ranges and then clear the values without touching cells with formulas. We found that with multiple cell range it is working as expected. But if the target range is single cell, all the constant cells in that sheet will be cleared which is not working is expected.

    Please refer attachment for reproduction. If we set targetRange as “B1”, we expect that only B1 should be cleared, but the result is not that.

    Please help to investigate this issue. Thank you.


  • Posted 17 May 2021, 8:08 pm EST


    We too can observe the issue at our end and escalated this to the developers. We will let you know as soon as there is any update on this. [Internal Tracking ID: DOCXLS-4133]

    Regards, Prabhat Sharma.

  • Posted 18 May 2021, 9:17 pm EST - Updated 29 September 2022, 6:35 am EST


    As per the developers, this is intended behavior. Because Excel has the same behavior and we implemented Excel’s behavior of SpecialCells, because:

    Excel VBA, VSTO, and web automation script compatibility are important to us. This feature can be used with the value of ActiveCell. When an end-user selects B1 and presses the “Search” button, the search scope is the whole worksheet. This behavior is consistent with Excel’s search dialog.

    Solution: 1: If a range represents a cell, use IRange.HasFormula property to check whether the cell has the formula. 2: If you must use SpecialCells, you can use “B1,B1” as the address of the range.

    I hope it helps. If you need any other help, please let us know.

    Regards, Prabhat Sharma.

  • Posted 19 May 2021, 10:35 am EST


    Thank you for your clarification.

    We understand that this is the expected behavior and will try the solution you provided. Your support is great as always.


Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels