[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
acrocephalus
General User

Joined: 25 Oct 2007
Posts: 34

 Posted: Wed Feb 16, 2011 6:08 am    Post subject: Count non empty rows 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
Robert Tucker
Moderator

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

Posted: Wed Feb 16, 2011 10:13 am    Post subject:

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.

_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot)
JohnV

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

Posted: Wed Feb 16, 2011 5:05 pm    Post subject:

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
acrocephalus
General User

Joined: 25 Oct 2007
Posts: 34

Posted: Thu Feb 17, 2011 3:49 am    Post subject:

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
JohnV

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

Posted: Thu Feb 17, 2011 8:07 am    Post subject:

 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")
acrocephalus
General User

Joined: 25 Oct 2007
Posts: 34

Posted: Fri Feb 18, 2011 1:24 am    Post subject:

Thank you so much, John. It works, however, when I close and then re-open the file I get this error:

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
JohnV

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

 Posted: Fri Feb 18, 2011 6:47 am    Post subject: 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.
acrocephalus
General User

Joined: 25 Oct 2007
Posts: 34

Posted: Fri Feb 18, 2011 7:25 am    Post subject:

Hello,
 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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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