Option Explicit
Sub CountColA()
Dim x As Long
Dim i As Long, lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
x = 0
For i = 1 To lr
If Range("A" & i) > 0 Then
x = x + 1
End If
Next i
MsgBox ("Total Number above zero is " & x)
End Sub
i have a column with numbers 0-10. i want to display in a desginated cell. the current streak. as in currently how many cells in a row are greater than 0. from the top. how do i do this?
Excel 2010 | |||
---|---|---|---|
A | |||
1 | 7 | ||
2 | 2 | ||
3 | 0 | ||
4 | 7 | ||
5 | 0 | ||
6 | 2 | ||
7 | 7 | ||
8 | 10 | ||
9 | 6 | ||
10 | 10 | ||
11 | 0 | ||
12 | 0 | ||
13 | 1 | ||
14 | 6 | ||
15 | 7 | ||
16 | 0 | ||
Sheet4 |
[table="width: 500"]
[tr]
[td]Function Streak()
Dim x As Long, Arr As Variant
Arr = Split(Application.Trim(Join(Evaluate("TRANSPOSE(IF(A1:A" & Cells(Rows.Count, "A").End(xlUp).Row & ">0,1,"" ""))"), "")))
For x = 0 To UBound(Arr)
If Len(Arr(x)) > Streak Then Streak = Len(Arr(x))
Next
End Function[/td]
[/tr]
[/table]
Which is why I made post number 3.i want to display in a desginated cell. the current streak. as in currently how many cells in a row are greater than 0
i have a column with numbers 0-10. i want to display in a desginated cell. the current streak. as in currently how many cells in a row are greater than 0. from the top. how do i do this?
It was a guess on my part which is why I specified to the OP what the code will return.Rick, hopefully you are right that the OP wants the longest streak as I am not sure exactly what they mean by
Give this UDF a try (install instructions in Message #4, note function name change though)...thanks for the replies. my intention was not the longest streak but the latest streak.
with data as listed below. the latest streak read from the top would be 3 in a row greater than 0. that is what i want to display in a seperate cell. how do i do this?
a1 3
a2 4
a3 8
a4 0
a5 1
a6 1
a7 1
a8 1
[table="width: 500"]
[tr]
[td]Function LatestStreak()
LatestStreak = Len(Split(Application.Trim(Join(Evaluate("TRANSPOSE(IF(A1:A" & Cells(Rows.Count, "A").End(xlUp).Row & ">0,1,"" ""))"), "")))(0))
End Function[/td]
[/tr]
[/table]