Maximum count of consecutive non-blank cells in a row

[jjoyce2]

I am preparing a dataset for analysis. It's a calendar of a sort, each cell representing a month over the course of 3 1/2 years. Each row is a subject's record. Using another dataset, I have already determined that each subject with 6 or more contiguous cells populated with data conform to a normal distribution.

I have an array of cells 42 cells wide and 2993 rows long. Each row is a distinct record. The maximum count of populated cells in any row (of 42) is 18 cells and the minimum count is 1.

I need to identify each row (of 2993) having a 6 or greater count of CONSECUTIVE, non-blank cells, that count of consecutive non-blank cells, and the first cell in that sequence of non-blank cells (two products.) If a row has more than one series of consecutive non-blank cells, I want the longer or longest of those series on that row.

I need help trying to accomplish this. Any suggestions?

[karedog]

Maybe :

Sub DataGenerator()
  Dim a(1 To 2993, 1 To 42), i As Long, j As Long
  For i = 1 To UBound(a, 1)
      For j = 1 To UBound(a, 2)
          If Rnd() > 0.5 Then a(i, j) = "X"
      Next j
  Next i
  Application.ScreenUpdating = False
    Range("A2").Resize(UBound(a, 1), UBound(a, 2)).Value = a
  Application.ScreenUpdating = True
End Sub
Sub GetMaxCount()
  Dim a(), b(), i As Long, j1 As Long, j2 As Long, c As Long, cf As Long, t As Long, tf As Long
  a = Range("A2").Resize(2993, 42).Value
  ReDim b(1 To UBound(a, 1), 1 To 2)
  For i = 1 To UBound(a, 1)
      cf = 0: tf = 0
      For j1 = 1 To UBound(a, 2)
          If Not IsEmpty(a(i, j1)) Then
             c = j1: t = 0
             For j2 = j1 To UBound(a, 2)
                 If Not IsEmpty(a(i, j2)) Then
                    t = t + 1
                 Else
                    j1 = j2
                    Exit For
                 End If
             Next j2
             If t > tf Then
                cf = c: tf = t
             End If
          End If
      Next j1
      If tf >= 6 Then
         b(i, 1) = cf
         b(i, 2) = tf
      End If
  Next i
  Application.ScreenUpdating = False
    Range("AR2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Application.ScreenUpdating = True
End Sub

[jjoyce2]

Thanks very much Karedog!

Let me check it out and see what we get...

[jjoyce2]

Thanks very much Karedog!

Let me check it out and see what we get...