OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Count non empty rows

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
acrocephalus
General User
General User


Joined: 25 Oct 2007
Posts: 34

PostPosted: Wed Feb 16, 2011 6:08 am    Post subject: Count non empty rows Reply with quote

Hello!
I have a matrix which ranges from C2:K1275. I need to count how many non empty rows are there. I know how to count non empty cells, but what about rows?
Cheers!

Dani
Back to top
View user's profile Send private message Visit poster's website
Robert Tucker
Moderator
Moderator


Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

PostPosted: Wed Feb 16, 2011 10:13 am    Post subject: Reply with quote

For C2:F9 this works:

Code:
=ROWS(C2:C9)-SUMPRODUCT(ISBLANK(C2:C9)*ISBLANK(D2:D9)*ISBLANK(E2:E9)*ISBLANK(F2:F9))


Don't know if there's a limit for SUMPRODUCT.

[Ref: http://www.excelbanter.com/showthread.php?p=819887 ]
_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Wed Feb 16, 2011 5:05 pm    Post subject: Reply with quote

This user defined function will do it but does not automatically update. An update can be done with Ctrl+Shift+F9.

The function is called in the spreadsheet with something like:
=NONBLANK("C2";"K1275")
Code:
Function NonBlank(TopLeft,LowerRight) as String
s = ThisComponent.CurrentController.ActiveSheet
rng = s.getCellRangeByName(TopLeft &":"& LowerRight)
c = rng.Columns.Count-1
For r = 0 to rng.Rows.Count -1
 rw = rng.getCellRangeByPosition(0,r,c,r)
 da = rw.getDataArray
 d = da(0,0)
 blk = true
 For cc = 0 to ubound(d)
  If d(cc) <> "" then blk = false : Exit For
 Next
 If blk = false then nb=nb+1
Next
NonBlank = nb
End Function
Back to top
View user's profile Send private message
acrocephalus
General User
General User


Joined: 25 Oct 2007
Posts: 34

PostPosted: Thu Feb 17, 2011 3:49 am    Post subject: Reply with quote

JohnV wrote:
This user defined function will do it but does not automatically update. An update can be done with Ctrl+Shift+F9.

The function is called in the spreadsheet with something like:
=NONBLANK("C2";"K1275")
Code:
Function NonBlank(TopLeft,LowerRight) as String
s = ThisComponent.CurrentController.ActiveSheet
rng = s.getCellRangeByName(TopLeft &":"& LowerRight)
c = rng.Columns.Count-1
For r = 0 to rng.Rows.Count -1
 rw = rng.getCellRangeByPosition(0,r,c,r)
 da = rw.getDataArray
 d = da(0,0)
 blk = true
 For cc = 0 to ubound(d)
  If d(cc) <> "" then blk = false : Exit For
 Next
 If blk = false then nb=nb+1
Next
NonBlank = nb
End Function


Thanks John! Is there a way to make the function auto-updatable? Furthermore, I'd rather prefer to cal it like =NONBLANK(C2:K1275) instead of =NONBLANK("C2";"K1275"). How can I do it? I have no idea on OO programming.
Cheers!

Dani
Back to top
View user's profile Send private message Visit poster's website
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Thu Feb 17, 2011 8:07 am    Post subject: Reply with quote

Quote:
Is there a way to make the function auto-updatable?
Yes, make this change
Code:
Function NonBlank(TopLeft,LowerRight,z)
and call the function with
=NONBLANK("C2";"K1275";C2:K1275).
Quote:
I'd rather prefer to cal it like =NONBLANK(C2:K1275)
That would pass the data in the array of cells to the function which would not work. You could put text of C2 and K1275 in say A1 & A2 and call it with
=NONBLANK(A1;A2)

EDIT:
An afterthought. I have been assuming that the range you might want to monitor might change and thus did not hard code it in the macro. If that's not true then you can change these lines and call with =NONBLANK(C2:K1275). This would take care of both your requested items.
Code:
Function NonBlank(z)

rng = s.getCellRangeByName("C2:K1275")
Back to top
View user's profile Send private message
acrocephalus
General User
General User


Joined: 25 Oct 2007
Posts: 34

PostPosted: Fri Feb 18, 2011 1:24 am    Post subject: Reply with quote

Thank you so much, John. It works, however, when I close and then re-open the file I get this error:
Quote:
Basic runtime error. Property or method not found: CurrentController

And it opens the file and the macro IDLE, but if I close the IDLE I can work with the file. How can I solve it? Finally, is there any nice tutorial for OpenOffice Basic? I may need it in the near future.
Cheers!

Dani
Back to top
View user's profile Send private message Visit poster's website
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Fri Feb 18, 2011 6:47 am    Post subject: Reply with quote

In order to look at your last problem I would have to know which of the previous suggested changes you put into affect and how you are calling the function.
Back to top
View user's profile Send private message
acrocephalus
General User
General User


Joined: 25 Oct 2007
Posts: 34

PostPosted: Fri Feb 18, 2011 7:25 am    Post subject: Reply with quote

Hello,
Thank you for your reply John. I've just made the modification you suggested
Code:
Function NonBlank(TopLeft,LowerRight,z)

and I'm calling it as
Code:
=NONBLANK("D2";"L1275";D2:L1275)

Note that I've changed the range as it has changed in the spreadsheet.
Cheers!

Dani
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group