Count cells greater than 0 from a range where some cell became hidden

[otrava18]

Hello ,

I try to count cells that are greater than 0 from a row where the columns are moving from hide to unhide. I need to ignore the hidden cells. Every solution that i use was not work. My cells that i need to count are from C25:L25.
I tried :
1. =COUNTIF(C25:L25,">0") -> not working because depends on a cell value i hide some columns.

2. =SUMPRODUCT(SUBTOTAL(103,OFFSET(C25:L25,ROW(C25:L25)-ROW(C25),,1)),--(C25:L25>0)) -> returns me only #value! (i don`t know why)

3. I have also a module -> doesn`t work because it count also if the cell is 0

Function COUNTVISIBLE(Rg)
    Dim xCount As Long
    Dim xCell As Range
    Application.Volatile
    For Each xCell In Rg
        If (Not xCell.EntireRow.Hidden) And (Not xCell.EntireColumn.Hidden) Then
            xCount = xCount + 1
        End If
    Next
    COUNTVISIBLE = xCount
End Function

Can you help me with an advise ?

Thank you all !

 

[DanteAmor]

The results should be updated by pressing the F9 key.
In the other modules you stop the macro with the End statement?

 

[otrava18]

The results should be updated by pressing the F9 key.
In the other modules you stop the macro with the End statement?

Not working the F9 for me. In all modules i have End Function in the end. Your code is working well only until i run another code. Even if the code is not linked with my result i receive value error. I receive a good result only after i double click on the cell where i have the formula and hit enter. I can share some picture if you want.

 

[DanteAmor]

I have other macros in other modules on the same sheet or on different sheets and they all work. Only if some macro is interrupted then the cell remains with #VALUE.
We would have to review the other macro to see which one has the problem.

 

[otrava18]

I have other macros in other modules on the same sheet or on different sheets and they all work. Only if some macro is interrupted then the cell remains with #VALUE.
We would have to review the other macro to see which one has the problem.

The blue shape has it`s code inside. I change the values of 4 cells from different sheets in the same workbook. If i click on it i receive error on A8. (A9 depend on A8).

 

[DanteAmor]

I already created your macro. By the way, do not put it on an image, it is difficult to copy it from an image.
I ran the macro and I have no problems.
Run your "ingropat" macro step by step (Press F8) and see what happens with the CountVisible function or Sum_Visible function

 

[otrava18]

I already created your macro. By the way, do not put it on an image, it is difficult to copy it from an image.
I ran the macro and I have no problems.
Run your "ingropat" macro step by step (Press F8) and see what happens with the CountVisible function or Sum_Visible function

Hello DanteAmor,

I discover the issue :D . In the Sub Ingropat () i add two lines. From that copy and paste values some cell was hide or unhide.

Application.ScreenUpdating = True
Application.CalculateFullRebuild

Now everything is working well. Your formula works fine.

Thank you again !

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.