hi,

I have the below macro which is almost there.

I need to stop the loop when the immediate cell above no longe
displays the result of the formula. I don't know how to test for thi
criteria.

thanks

Sub test()
Do
If IsEmpty(ActiveCell) Then
Selection.FormulaArray = _

"=IF(ROW()-ROW(NoBlanksRange)+1ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"""",INDIRECT(ADDRESS(SMAL L((IF(BlanksRange<"""",ROW(BlanksRange),ROW()+ROW S(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))"
If ActiveCell.Offset(-2, 0).Value = "" An
ActiveCell.Offset(-1, 0).Value = "" Then Exit Sub
End If
ActiveCell.Offset(1, 0).Select

Loop Until IsNull(ActiveCell.Offset(-1, 0))



End Su

--
Message posted from http://www.ExcelForum.com