Count continuous cells as one in a column

[ksillas]

Hello,

I need to count "grouped" cells with values as one. Column "I" has bunch of 1's and 0's if it was on or off. I already deleted all the 0's and was left with 1's and empty cells in between. For example:

[TABLE="class: grid, width: 200, align: center"]
<tbody>[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

This has 3 "groups" so the end count would be 3.

To do it manually I would press CTRL+Down Arrow twice (once to reach the first cell with content, the second one to reach the last cell with content) and that would count as 1 "group."


If you guys need more information I'm glad to explain further. The excel file (per day) contains around 200K rows , it's like a log file that records every 300ish milliseconds..

 

[Fluff]

If the cells contain values rather than formulae try

Sub ksillas()
   MsgBox Range("I:I").SpecialCells(xlConstants).Areas.Count
End Sub