If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




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= 
Ads 
#2




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 nonblank. 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= > 
#3




Count filled data rows until empty
In >, Dallman Ross <[email protected]>
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= 
#4




Count filled data rows until empty
If you want a function that give you the count of contiguous, nonblank
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 <[email protected]> > 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




Count filled data rows until empty
In >, Ron
Coderre > spake thusly: > If you want a function that give you the count of contiguous, nonblank > 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 <[email protected]> > > 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




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, nonblank > > 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 <[email protected]> > > > 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




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") ;> 
Thread Tools  
Display Modes  


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 greyfilled cells are in a row ?  AnthonyG  Excel Worksheet Functions  1  February 4th 05 11:08 AM 
Count Rang of FilledIn Cells  Ginger  Excel Worksheet Functions  3  December 22nd 04 09:15 PM 