Home 
Search 
Today's Posts 
#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= 
#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") ; 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Macro Request: Fill in empty cells with previous Filled cell in column  Excel Worksheet Functions  
How do I get a count of number of cells filled in?  Excel Discussion (Misc queries)  
Copy Data From Filled to Empty Cells  Excel Discussion (Misc queries)  
Can I count how many greyfilled cells are in a row ?  Excel Worksheet Functions  
Count Rang of FilledIn Cells  Excel Worksheet Functions 