View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre Ron Coderre is offline
external usenet poster
 
Posts: 698
Default Count filled data rows until empty

Hi, dman

I'm not sure what all the rules are, but try this:

Function CountContiguous(rStartCell As Range) As Long
Dim lngVal As Long
With WorksheetFunction
If .CountA(rStartCell) = 0 Then
'StartCell is Blank
lngVal = 0
ElseIf .CountA(rStartCell.Offset(RowOffset:=1)) = 0 Then
'Cell immediately below StartCell is blank
lngVal = 1
Else
lngVal = .CountA(Range(rStartCell, rStartCell.End(Direction:=xlDown)))
End If
End With
CountContiguous = lngVal
End Function

Note: That UDF considers cells containing only an apostrophe (') or an empty
text string ("") as non-blank.

Test it with this:
Sub TestCountContig()
MsgBox CountContiguous(ActiveCell)
End Sub

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"Dallman Ross" wrote:

In VBA, how can I count the filled cells in a column until
the first empty cell? Thanks for any help, folks.

=dman=