| View previous topic :: View next topic |
| 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 |
|
| Back to top |
|
 |
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.
[Ref: http://www.excelbanter.com/showthread.php?p=819887 ]
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot) |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8979
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 |
|
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8979
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") |
|
|
| Back to top |
|
|
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:
| 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 |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8979
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. |
|
| Back to top |
|
|
acrocephalus
General User
Joined: 25 Oct 2007
Posts: 34
|
| Posted: Fri Feb 18, 2011 7:25 am Post subject: |
|
|
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 |
|
|
|
