Behavior of IRange.SpecialCells

Behavior of IRange.SpecialCells

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

• 

  yyoshimura

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 17 May 2021, 11:12 am EST

  Hi,

  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.

  Regards

  gcexcelperformance.zip

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 17 May 2021, 8:08 pm EST

  Hello,

  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.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

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

  Hello,

  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.

  

  880×509 18.2 KB

  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.

  

  900×488 17.6 KB

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

  Regards,
  
  Prabhat Sharma.

• 

  yyoshimura

  • Post Options:
  • Link
    Copy

  Posted 19 May 2021, 10:35 am EST

  Hi

  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.

  Regards