Option Explicit
Enum SIGNValue
Positive = True
Negative = False
NotNumeric = -2
End Enum
Function BlockRowsCount(DatabaseRange As Range, valueRow As Range) As Integer
Dim dblRow As Double
Dim SignCurr As SIGNValue
Dim iCnt As Integer
SignCurr = getSign(DatabaseRange, valueRow.Row)
If Not SignCurr = SIGNValue.NotNumeric Then
dblRow = valueRow.Row + 1
iCnt = -1
Do
dblRow = dblRow - 1
iCnt = iCnt + 1
Loop While dblRow > 1 And getSign(DatabaseRange, dblRow - 1) = SignCurr
dblRow = valueRow.Row - 1
Do
iCnt = iCnt + 1
dblRow = dblRow + 1
Loop While dblRow <= DatabaseRange.Rows.Count + wrapper(DatabaseRange).Row - 1 And getSign(DatabaseRange, dblRow + 1) = SignCurr
Else
iCnt = 0
End If
BlockRowsCount = iCnt
End Function
Private Function getSign(DatabaseRange As Range, dblRow As Double) As SIGNValue
Dim varValue As Variant
varValue = DatabaseRange.Cells(dblRow - wrapper(DatabaseRange).Row + 1, 2).Value
If IsNumeric(varValue) And Not IsEmpty(varValue) Then
getSign = IIf(CBool(Abs(varValue) = varValue), SIGNValue.Positive, SIGNValue.Negative)
Else
getSign = SIGNValue.NotNumeric
End If
End Function
Private Function wrapper(current As Range) As Range
Set wrapper = Sheets(current.Parent.Name).Cells(current.Cells(1).Row, current.Cells(1).Column)
End Function