Count filled data rows until empty

  • Thread starter Thread starter Dallman Ross
  • Start date Start date
D

Dallman Ross

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

=dman=
 
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 <dman@localhost.> said:
In VBA, how can I count the filled cells in a column until
the first empty cell? Thanks for any help, folks.
Click to expand...

Man, but it is amazingly quiet in here. I am having a monologue
lately.

After much scrambling around on the net I came up with this, thanks
in no small part to the great MVP tip pages over at ozgrid.com.

Particularly helpful was http://www.ozgrid.com/VBA/VBALoops.htm

So here is what I have:

Public Function RcountTillBlank()
Cells.Find(What:="", After:=[A1], LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False).Activate
RcountTillBlank = ActiveCell.Row - 1
End Function


That works and I like it. But if anybody can tell me how to make it
take an argument for the range to start the search from, that would be
super. Or any other comments about the above, for that matter.

=dman=
 
If you want a function that give you the count of contiguous, non-blank
cells, beginning with a StartCell....

Put this code in a General Module:

Function CountContiguous(rStartCell As Range) As Long
Application.Volatile
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

Then....in your worksheet

This formula returns the count:
B1: =countcontiguous(A1)

With
A1: Alpha
A2: Bravo
A3: Charlie
A4: Delta
A5: 1
A6: 2
A7: 3

In the above case, the formula returns 7

With this data
A1: Alpha
A2: Bravo
A3: Charlie
A4: (blank)
A5: 1
A6: 2
A7: 3

the formula returns 3

With this data:
A1: Alpha
A2: (blank)
A3: Charlie
A4: (blank)
A5: 1
A6: 2
A7: 3

the formula returns 1

And if A1 is blank, the formula returns 0

Does that help?
***********
Regards,
Ron

XL2003, WinXP


Dallman Ross said:
Dallman Ross <dman@localhost.> said:
In VBA, how can I count the filled cells in a column until
the first empty cell? Thanks for any help, folks.
Click to expand...

Man, but it is amazingly quiet in here. I am having a monologue
lately.

After much scrambling around on the net I came up with this, thanks
in no small part to the great MVP tip pages over at ozgrid.com.

Particularly helpful was http://www.ozgrid.com/VBA/VBALoops.htm

So here is what I have:

Public Function RcountTillBlank()
Cells.Find(What:="", After:=[A1], LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False).Activate
RcountTillBlank = ActiveCell.Row - 1
End Function


That works and I like it. But if anybody can tell me how to make it
take an argument for the range to start the search from, that would be
super. Or any other comments about the above, for that matter.

=dman=
Click to expand...
 
Ron said:
If you want a function that give you the count of contiguous, non-blank
cells, beginning with a StartCell....

Put this code in a General Module:
Click to expand...

That's super, Ron! Thanks a bunch. Perfect.

One small remaining question: what is a "General"
Module, as opposed to the typical modules I've been using?

Okay, two questions. Why are we making it Application.Volatile?
It seems to work fine without that for my purposes.

=dman=

===========================
Function CountContiguous(rStartCell As Range) As Long
Application.Volatile
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

Then....in your worksheet

This formula returns the count:
B1: =countcontiguous(A1)

With
A1: Alpha
A2: Bravo
A3: Charlie
A4: Delta
A5: 1
A6: 2
A7: 3

In the above case, the formula returns 7

With this data
A1: Alpha
A2: Bravo
A3: Charlie
A4: (blank)
A5: 1
A6: 2
A7: 3

the formula returns 3

With this data:
A1: Alpha
A2: (blank)
A3: Charlie
A4: (blank)
A5: 1
A6: 2
A7: 3

the formula returns 1

And if A1 is blank, the formula returns 0

Does that help?
***********
Regards,
Ron

XL2003, WinXP


Dallman Ross said:
Dallman Ross <dman@localhost.> said:
In VBA, how can I count the filled cells in a column until
the first empty cell? Thanks for any help, folks.
Click to expand...

Man, but it is amazingly quiet in here. I am having a monologue
lately.

After much scrambling around on the net I came up with this, thanks
in no small part to the great MVP tip pages over at ozgrid.com.

Particularly helpful was http://www.ozgrid.com/VBA/VBALoops.htm

So here is what I have:

Public Function RcountTillBlank()
Cells.Find(What:="", After:=[A1], LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False).Activate
RcountTillBlank = ActiveCell.Row - 1
End Function


That works and I like it. But if anybody can tell me how to make it
take an argument for the range to start the search from, that would be
super. Or any other comments about the above, for that matter.

=dman=
Click to expand...
Click to expand...
 
A "General Module" is just a regular module.....as opposed to a workbook
module, worksheet module, or class module.

In my brief testing of the function, it didn't seem to always recalculate.
After I put the Application.Volatile statement in, it worked fine for me. If
you really don't need it, comment it out.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


Dallman Ross said:
Ron said:
If you want a function that give you the count of contiguous, non-blank
cells, beginning with a StartCell....

Put this code in a General Module:
Click to expand...

That's super, Ron! Thanks a bunch. Perfect.

One small remaining question: what is a "General"
Module, as opposed to the typical modules I've been using?

Okay, two questions. Why are we making it Application.Volatile?
It seems to work fine without that for my purposes.

=dman=

===========================
Function CountContiguous(rStartCell As Range) As Long
Application.Volatile
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

Then....in your worksheet

This formula returns the count:
B1: =countcontiguous(A1)

With
A1: Alpha
A2: Bravo
A3: Charlie
A4: Delta
A5: 1
A6: 2
A7: 3

In the above case, the formula returns 7

With this data
A1: Alpha
A2: Bravo
A3: Charlie
A4: (blank)
A5: 1
A6: 2
A7: 3

the formula returns 3

With this data:
A1: Alpha
A2: (blank)
A3: Charlie
A4: (blank)
A5: 1
A6: 2
A7: 3

the formula returns 1

And if A1 is blank, the formula returns 0

Does that help?
***********
Regards,
Ron

XL2003, WinXP


Dallman Ross said:
In <[email protected]>, Dallman Ross <dman@localhost.>
spake thusly:

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

Man, but it is amazingly quiet in here. I am having a monologue
lately.

After much scrambling around on the net I came up with this, thanks
in no small part to the great MVP tip pages over at ozgrid.com.

Particularly helpful was http://www.ozgrid.com/VBA/VBALoops.htm

So here is what I have:

Public Function RcountTillBlank()
Cells.Find(What:="", After:=[A1], LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False).Activate
RcountTillBlank = ActiveCell.Row - 1
End Function


That works and I like it. But if anybody can tell me how to make it
take an argument for the range to start the search from, that would be
super. Or any other comments about the above, for that matter.

=dman=
Click to expand...
Click to expand...
Click to expand...
 
Ron said:
A "General Module" is just a regular module.....as opposed to a
workbook module, worksheet module, or class module.

In my brief testing of the function, it didn't seem to always
recalculate. After I put the Application.Volatile statement in,
it worked fine for me. If you really don't need it, comment it
out.

Does that help?
Click to expand...

Yuppers. It's super, Ron. Thanks again.

=dman= (now wondering if your last name means "coder") ;->
 
Back
Top