Forum Discussion

sandeepvr's avatar
sandeepvr
Copper Contributor
Nov 22, 2022
Solved

counting protected cells in a range of cells

Hi,

Is there a way to count the number of protected cells in a range of cells?

E.g. say the range of cells is A1:A10 in which some cells are protected and some are not. I want to get the result of number of protected cells in this range. 

 

I tried with COUNTIF(A1:A10, CELL("protect")=1), but it did not give the desired result. I guess my understanding and usage of COUNTIF for the above mentioned requirement is incorrect. May be some other function has to be used. Please help. 

 

Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Nov 22, 2022

    sandeepvr 

    That syntax is not valid, but unfortunately, the CELL function does not return an array, just a single value if you specify  a multi-cell range.

    You might create the VBA function listed below in a module in the Visual Basic Editor. Don't forget to save the workbook as a macro-enabled workbook (.xlsm) and to allow macros when you open it.

    Function CountLocked(rng As Range) As Long
    Dim cel As Range
    For Each cel In rng
        CountLocked = CountLocked - cel.Locked
    Next cel
End Function

    Use like this:

    =CountLocked(A1:A10)

2 Replies

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 22, 2022

    sandeepvr 

    That syntax is not valid, but unfortunately, the CELL function does not return an array, just a single value if you specify  a multi-cell range.

    You might create the VBA function listed below in a module in the Visual Basic Editor. Don't forget to save the workbook as a macro-enabled workbook (.xlsm) and to allow macros when you open it.

    Function CountLocked(rng As Range) As Long
    Dim cel As Range
    For Each cel In rng
        CountLocked = CountLocked - cel.Locked
    Next cel
End Function

    Use like this:

    =CountLocked(A1:A10)

Resources