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]

Try this

Function COUNTVISIBLE(Rg As Range)
  Dim xCount As Long, c As Range
  For Each c In Rg.SpecialCells(xlCellTypeVisible)
    If c.Value > 0 Then
      COUNTVISIBLE = COUNTVISIBLE + 1
    End If
  Next
End Function

 

[DanteAmor]

Sorry, in other tests it didn't work, try like this:

Function COUNTVISIBLE(Rg As Range)
  Dim xCount As Long, c As Range, m As String
  For Each c In Rg.SpecialCells(xlCellTypeVisible)
    If c.EntireRow.Hidden = False And c.EntireColumn.Hidden = False Then
      If c.Value > 0 Then
        m = c.Address
        xCount = xCount + 1
      End If
    End If
  Next
  COUNTVISIBLE = xCount
End Function

 

[otrava18]

Sorry, in other tests it didn't work, try like this:

Function COUNTVISIBLE(Rg As Range)
  Dim xCount As Long, c As Range, m As String
  For Each c In Rg.SpecialCells(xlCellTypeVisible)
    If c.EntireRow.Hidden = False And c.EntireColumn.Hidden = False Then
      If c.Value > 0 Then
        m = c.Address
        xCount = xCount + 1
      End If
    End If
  Next
  COUNTVISIBLE = xCount
End Function

Thank you very much , is working grate !

 

[DanteAmor]

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

 

[otrava18]

Now i face another issue. I have a shape that depends on the result of the above module. This shape won`t change the color only if i double click on a cell and hit the enter. Do you have an idea how to resolve this in VBA ?

Private Sub Worksheet_Change(ByVal Target As Range)
If Worksheets("VIDEO 2 FIRE").Range("V2").Value = "READY" Then
ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.ForeColor.RGB = RGB(154, 192, 74)
ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.BackColor.RGB = RGB(154, 192, 74)
Else
ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.ForeColor.RGB = RGB(204, 64, 61)
ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.BackColor.RGB = RGB(204, 64, 61)
End If

 

[DanteAmor]

This shape won`t change the color only if i double click on a cell

I'm not sure what you want.
Do you need to double click on a cell and then the shape change color?
If so you need to use the event

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

But this is a different topic than this thread, I recommend creating a new thread so that you receive the specific help of what it requires. I also recommend that you explain your requirement in more detail in the thread.

 

[otrava18]

I'm not sure what you want.
Do you need to double click on a cell and then the shape change color?
If so you need to use the event

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

But this is a different topic than this thread, I recommend creating a new thread so that you receive the specific help of what it requires. I also recommend that you explain your requirement in more detail in the thread.

No, i`m sorry if i was not so clear. I have a shape that is colored in red and green. With your code i receive a sum of a row with 10 cells. If this number of unhide cells (your code) is equal with a "x" cell (let`s say "A1" then transform the shape in red to green and if the number is not equal from green to red. This is working now. What is not working : if i use your code and after i hide some columns the shape won`t change the color only if i do like a refresh , double click on any cell and hit the enter button. After this the shape it`s refreshing the color. I don`t know how to do this refresh in VBA. If is another topic i will create a new thread.

Thank you for you time !

 

[DanteAmor]

Youre welcome.

 

[otrava18]

I'm not sure what you want.
Do you need to double click on a cell and then the shape change color?
If so you need to use the event

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

But this is a different topic than this thread, I recommend creating a new thread so that you receive the specific help of what it requires. I also recommend that you explain your requirement in more detail in the thread.

Youre welcome.

I`m sorry for come back but i think you can help me with your code. Is working well until i use a module (any module) from my workbook. After this i receive as result of your formula #Value!. I have a similar module that calculate the sum of unhide cells that is working well. I saw that you are very good in VBA and maybe you can help me to modify from sum to count. Or if you have any idea about value error ?

Function Sum_Visible(Cells_To_Sum As Object)
    Dim vTotal As Variant
    Application.Volatile
    vTotal = 0
    For Each cell In Cells_To_Sum
        If Not cell.Rows.Hidden Then
            If Not cell.Columns.Hidden Then
                vTotal = vTotal + cell.Value
            End If
        End If
    Next
    Sum_Visible = vTotal
End Function

Thank you @DanteAmor !