Hi Tom,

Thank you very much for your time and assistance. I have gone with RD's
solution but your user-defined function will most definitely be of use. Thank
you again for taking the time.

Cheers,
Sam

Tom Hutchins wrote:
You could use a user-defined function like the following:

Public Function CountConsecZeros(RangeToCheck As Range) As Long
Dim c As Range, TmpCnt As Long, CurrMax As Long
If RangeToCheck.Columns.Count 1 Then
MsgBox "RangeToCheck must be in a single column", , "Error"
CountConsecZeros = -1
Exit Function
End If
CurrMax = 0
TmpCnt = 0
For Each c In RangeToCheck
Select Case c.Value
Case 0
TmpCnt = TmpCnt + 1
Case Else
If TmpCnt CurrMax Then
CurrMax = TmpCnt
End If
TmpCnt = 0
End Select
Next c
CountConsecZeros = CurrMax
End Function

Paste this function in a VBA module in your workbook. It is called like this:
=CountConsecZeros(A1:A39)

If you are new to user-defined functions (macros), this link to Jon
Peltier's site may be helpful:
http://peltiertech.com/WordPress/200...e-elses-macro/

Hope this helps,

Hutch


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200806/1