Re: formatting inputbox

Nicole Seibert wrote:

If in case, dear helper, you are interested. I tried to count the
rows from the bottom up starting at a point in the data in which
there are four consecutive blank cells in column b. Using some
manipulation of ActiveCell offset several times over in which all of
them are blank, etc. That was yesterday evening. If you believe you
have a more fruitful rendition I am all ears.


Are you counting from the "four consecutive cells" that are closest to the
top? I.e. if B8:B11 are blank and B13:B16 are blank, would the answer be 7
or 12? If 7, then the below code should do it. If 12, you'll need to
change your loop to a For Next loop and loop backward from the bottom.

Function CountRows(sh As Work***) As Long

Dim rCell As Range

For Each rCell In Intersect(sh.Columns(2), sh.UsedRange).Cells
If Not IsNull(rCell.Resize(4).FormulaArray) Then
If Len(rCell.Resize(4).FormulaArray) = 0 Then
If rCell.Row > 1 Then
CountRows = rCell.Offset(-1, 0).Row
Else
CountRows = 0
End If
Exit Function
End If
End If
Next rCell

End Function


--
*** Kusleika
MS MVP - Excel
www.dailydoseofexcel.com


.