SpecialCells with SpecialCellType.Formulas of single cell is not correct

Posted by: yyoshimura on 7 December 2022, 4:33 pm EST

    • Post Options:
    • Link

    Posted 7 December 2022, 4:33 pm EST


    We are using IRange.SpecialCells(SpecialCellType.Formulas) to get the cells with formulas in the range. With multiple cells range it is working as expected but with single cell the result is not as we expected. Please refer attachment for reproduction.

    Please help to investigate this issue. Thank you.



  • Posted 11 December 2022, 3:23 pm EST


    Thank you for reporting this issue. We too can observe the issue and escalate this to the development team for investigation. We will let you know as soon as we get any update on this from their end.

    [Internal Tracking ID: DOCXLS-7228]


    Prabhat Sharma.

  • Posted 13 December 2022, 9:24 pm EST - Updated 13 December 2022, 9:29 pm EST

    Hello Yukinori,

    As per the developers, This behavior is intended. Because Microsoft Excel looks in all cells in the worksheet of the specified Range if the Range is a cell.

    Sub Macro1()


    Range(“A1”).FormulaR1C1 = “=1+2”

    Range(“A2”).FormulaR1C1 = “=2+3”

    Set a3FindFormulas = Range(“A3”).SpecialCells(xlCellTypeFormulas)

    MsgBox a3FindFormulas.Address

    End Sub

    f you want to check whether a range has formula cells, you need to use Range.Count and Range.HasFormula first.

    If Range.Count =1 then use Range.HasFormula . Otherwise, use Range.SpecialCells.

    private static IRange FindFormulaCellsExact(IRange lookIn)


    if (lookIn == null)


    return lookIn;


    if (lookIn.Count < 2)


    return lookIn.HasFormula ? lookIn : null;


    return lookIn.SpecialCells(SpecialCellType.Formulas);



    Prabhat Sharma.

  • Posted 14 December 2022, 11:56 am EST

    Thank you for your detailed explanation and it really helps. We can try the provided method to get the exact result.

Need extra support?

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

Learn More

Forum Channels