Start and stop counting at the zero value Closed

Question

Hello, 
How to insert a COUNTIF function to count values once it reaches to the first zero value to stop. And count again the cells between the first zero and second zero. Any help will be appreciated


System Configuration: Windows / Chrome 97.0.4692.71

TrowaD · Answer

Hi Naima,

Not sure if this can be done with a formula, but it definitely can with a macro code.

The code will start counting from A1 until a zero is found. Then the counting result is placed in the cell next to the zero (so in column B).

To better explain the result, here is a screenshot:
 

Here is the code:
Sub RunMe()
Dim mCount As Long

For Each cell In Range("A1:A" & Range("A1").End(xlDown).Row)
    If cell.Value <> 0 Then mCount = mCount + 1
    If cell.Value = 0 Then
        cell.Offset(0, 1).Value = mCount
        mCount = 0
    End If
Next cell
End Sub

Let us know if alterations are desired.

Best regards,
Trowa

NaimaHersi · Answer

Dear Trowa. I have encountered slight problem. The result column has what is needed but also has zeros which was not meant to appear. How can I remove the zeros from the result column.

TrowaD · Answer

Hi Naima, Great job making the code work. They can be intimidating the first time you encounter them. From your feedback I get you want 2 additional things to happen: 1. Count the zeros in the source column. 2. No zeros in the result column. Result will look like this: Here is the code: Sub RunMe() Dim mCount, zCount As Long For Each cell In Range("A1:A" & Range("A1").End(xlDown).Row) If cell.Value <> 0 Then mCount = mCount + 1 If cell.Value = 0 Then zCount = zCount + 1 If mCount <> 0 Then cell.Offset(0, 1).Value = mCount mCount = 0 End If End If Next cell Range("A1").End(xlDown).Offset(0, 2).Value = zCount End Sub Feel free to ask if something in unclear. Best regards, Trowa

Start and stop counting at the zero value

3 responses

Similar discussions

Subscribe To Our Newsletter!