Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 390
Default Count filled data rows until empty

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

=dman=
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Count filled data rows until empty

Formula:
[list=1][*]We declare two variablesand counti will be used to loop through the rows in the column, and count will keep track of the number of filled cells.[*]We set i to 1 and count to 0.[*]We use Do While loop to check if the value in cell A1 (or the first cell in your columnis not empty, and if i is less than or equal to the last row number where your data ends. If both conditions are truewe increment the count variable and i by 1, and continue the loop.[*]Once we encounter an empty cell or reach the last rowthe loop stops.[*]We display a message box with the count variablewhich contains the number of filled cells in the column.[/list] 
Sure, I can help you with that!

You can use a simple loop in VBA to count the filled cells in a column until the first empty cell. Here's an example code that you can modify to fit your specific needs:

Formula:
Sub CountFilledCells()
    
Dim i As Integer
    Dim count 
As Integer
    
    i 
1
    count 
0
    
    
' Replace "A" with the column letter you want to count
    ' 
Replace "1" with the row number where your data starts
    
' Replace "100" with the last row number where your data ends
    Do While Range("A" & i).Value < "" And i <= 100
        count = count + 1
        i = i + 1
    Loop
    
    ' 
The count variable now contains the number of filled cells in the column
    MsgBox 
"The number of filled cells is: " count
End Sub 


Here's how the code works:
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
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=

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 390
Default Count filled data rows until empty

In , 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=
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default Count filled data rows until empty

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" wrote:

In , 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=



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 390
Default Count filled data rows until empty

In , Ron
Coderre spake thusly:

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:


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" wrote:

In , 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=

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default Count filled data rows until empty

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" wrote:

In , Ron
Coderre spake thusly:

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:


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" wrote:

In , 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=


  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 390
Default Count filled data rows until empty

In , Ron
Coderre spake thusly:

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?


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

=dman= (now wondering if your last name means "coder") ;-
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro Request: Fill in empty cells with previous Filled cell in column Artis Excel Worksheet Functions 2 June 25th 07 08:30 PM
How do I get a count of number of cells filled in? Meenie Excel Discussion (Misc queries) 3 January 10th 07 10:16 PM
Copy Data From Filled to Empty Cells Sheikh Saadi Excel Discussion (Misc queries) 0 November 10th 05 08:21 PM
Can I count how many grey-filled cells are in a row ? AnthonyG Excel Worksheet Functions 1 February 4th 05 11:08 AM
Count Rang of Filled-In Cells Ginger Excel Worksheet Functions 3 December 22nd 04 09:15 PM


All times are GMT +1. The time now is 03:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"