Count visible cells criteria in VBA

[Av8tordude]

I've done multiple search combination to find the answer, but unfortunately nothing matches what I looking for. I'm auto-filtering my data and I would like to count the number cells that is greater than 0 in VBA. Can someone assist.

Thank you kindly

 

[Rick Rothstein]

Hi Rick,

the output results needs to be displayed in a userform label. Can you tell me how to display it in a userform label? Thank you for help

First off, here is new code taking into account your specified column (P) and desire to be able to count numbers greater than and also less than zero...

[table="width: 500"]
[tr]
	[td]Function ZeroCount(Symbol As String)
  Dim Ar As Range
  For Each Ar In Range("P18", Cells(Rows.Count, "P").End(xlUp)).SpecialCells(xlVisible).Areas
    ZeroCount = ZeroCount + WorksheetFunction.CountIf(Ar, Symbol & "0")
  Next
End Function[/td]
[/tr]
[/table]

The argument you pass to the function is either "<" to count cells less than 0 or ">" to count cells greater than 0. You can also pass in "=" if you wanted to count the number of zeroes as well. The function assumes the worksheet you want to count is the active worksheet (this is in keeping with the code you posted so far). To use this function in your UserForm, simply assign the function to the Label's Caption property. For example,

Label1.Caption = ZeroCount(">")

to count cells greater than 0; or...

Label2.Caption = ZeroCount("<")

to count cells less than 0.

 

[Fluff]

You are changing the caption

CCI.Caption = Win & "/" & Loss
    
CCI.Caption = Format(WorksheetFunction.Subtotal(1, Range("L18:L" & LRow)), "$#,0.00")

 

[Av8tordude]

Thank you Rick...Works Great!... Thank you very much for your help.

First off, here is new code taking into account your specified column (P) and desire to be able to count numbers greater than and also less than zero...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function ZeroCount(Symbol As String)
  Dim Ar As Range
  For Each Ar In Range("P18", Cells(Rows.Count, "P").End(xlUp)).SpecialCells(xlVisible).Areas
    ZeroCount = ZeroCount + WorksheetFunction.CountIf(Ar, Symbol & "0")
  Next
End Function[/TD]
[/TR]
</tbody>[/TABLE]

The argument you pass to the function is either "<" to count cells less than 0 or ">" to count cells greater than 0. You can also pass in "=" if you wanted to count the number of zeroes as well. The function assumes the worksheet you want to count is the active worksheet (this is in keeping with the code you posted so far). To use this function in your UserForm, simply assign the function to the Label's Caption property. For example,

Label1.Caption = ZeroCount(">")

to count cells greater than 0; or...

Label2.Caption = ZeroCount("<")

to count cells less than 0.

 

[Av8tordude]

After you pointed out what was happening, however each time created a new filter requirement, it still doubled the output of the previous results. That being said...I really appreciate all the work you do for this forum. It has been a great value in learning.

You are changing the caption

CCI.Caption = Win & "/" & Loss
    
CCI.Caption = Format(WorksheetFunction.Subtotal(1, Range("L18:L" & LRow)), "$#,0.00")

 

[Fluff]

You're welcome & thanks for the feedback