Counting 6 consecutive 0's

[bhenderson]

Hello all,

I am not sure if this is more a function or a programming question, but I have a sample attached where I am trying to count 6 consecutive 0's in each row to help me identify trends. I am hoping for my end result to give me something akin to a pivot table that would have...

ID # Times 6 consecutive 0's counted
1 0
2 2
3 4
4 0


Any ideas for how to get from the sample to the intended result?

I tried using frequency functions but I am not quite getting it yet.

Thank you,
Ben

[rylo]

Ben

some questions

1) How are you intending to determine the range to evaluate, given the are all those trailing zeros.
2) How do you quantify 6 zero in a row strings? Say you have zeros in B-H, does that make 2 strings of 6 zeros (B-G and E-H) or 1? Using your example file, what should be the result for row 24, and how is that result calculated?

rylo

[bhenderson]

Hi Rylo

1) For most records I need the entire date range. If I could have a stop perameter that said if there are 45+ consecutive 0's flag this row that would be great.

2) B-H would be one string - good question. And so I would see 26 separate strings of 6 consecutive 0's. But, going back to Q1 there are also 90+ consecutive 0's at the end so I would love to have it notify me that the row may have issues. (i.e. highlight or put some sort of indicator in a new column)

Does that make sense?

I attached the sample with how I counted row 24

[rylo]

Hi

here goes

Sub aaa()
  lastcol = Cells(2, Columns.Count).End(xlToLeft).Column
  For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row
    sixcnt = 0
    sixcnttot = 0
    For j = 2 To lastcol - 1
      If Cells(i, j) <> 0 Then sixcnt = 0
      If Cells(i, j) = 0 Then sixcnt = sixcnt + 1
      If sixcnt = 6 Then
        sixcnttot = sixcnttot + 1
        sixcnt = 0
      End If
    Next j
    Cells(i, lastcol + 1).Value = sixcnttot
    If sixcnt >= 45 Then Cells(i, lastcol + 1).Interior.ColorIndex = 3
    If sixcnt >= 90 Then Cells(i, 1).EntireRow.Interior.ColorIndex = 3
  
  Next i
End Sub

rylo

[kvsrinivasamurthy]

Pl see attached file Sheet1(2).
If you run macro it counts continous zeros in each row and enters the number in the last column of same row.
Any clarifications welcome.