Start and stop counting at the zero value
Closed
Naima
-
Jan 11, 2022 at 06:07 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jan 13, 2022 at 11:31 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jan 13, 2022 at 11:31 AM
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
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
Related:
- Zcounte
- Start survey - Guide
- Start up sound changer - Guide
- Start steam in big picture mode - Guide
- Start money cs 1.6 - Guide
- How to start a listening party on spotify pc - Guide
3 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Jan 11, 2022 at 11:46 AM
Jan 11, 2022 at 11:46 AM
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:
Let us know if alterations are desired.
Best regards,
Trowa
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
Posts
1
Registration date
Wednesday January 12, 2022
Status
Member
Last seen
January 12, 2022
Jan 12, 2022 at 12:28 PM
Jan 12, 2022 at 12:28 PM
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
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Jan 13, 2022 at 11:31 AM
Jan 13, 2022 at 11:31 AM
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:
Feel free to ask if something in unclear.
Best regards,
Trowa
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
Jan 12, 2022 at 04:46 AM
Jan 12, 2022 at 11:57 AM
I have tried and it worked well. Thank you very much. But again, I couldnot make my own code for counting only the zeros and leaving any value greater than zero. Your help will be very much appreciated.