Counting rows
-
- Posts: 1
- Joined: Fri Sep 03, 2010 2:23 pm
Counting rows
I want to count the number or rows (or columns). I don't want to use a formula and this is a non-conditional count. It's just looking at the spreadsheet and counting. I'm a new OO user and I've searched but can't find a simple solution. In Excel you click and drag, the count displays. I'm looking for something simple like that. I do not want to modify my spreadsheet.
OpenOffice 3.2.1 on Windows XP
Re: Counting rows
Stay with Ecxel if this particular feature is really important for you.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Counting rows
As long as the cells you're counting contain data, you can see a count in the status bar. Right click the "Standard Formula" area (see Help, Contents, Spreadsheets, Command and Menu Reference, Toolbars, Status Bar) and select COUNTA (not COUNT). This shows the number of non-empty cells in the selection. If you have column headings, selecting some of them shows the number of columns; selecting cells from a specific column shows the number of rows; selecting a rectangular area shows the count of non-empty cells in it. If all the cells in the selected area contain data, it's the product of the rowsand column counts.
Of course, simple math quickly gives the number of selected rows. If I select B33:D41, this is shown in the Name Box in the Formula Bar and simple math gives the number of columns as 9 (1+41-33). If the numbers get messy, just open a new spreadsheet and enter a formula, say =1+5302-3795 to count rows 3795 through 5302, which is more work, but perhaps acceptable if this isn't information you need often.
Of course, simple math quickly gives the number of selected rows. If I select B33:D41, this is shown in the Name Box in the Formula Bar and simple math gives the number of columns as 9 (1+41-33). If the numbers get messy, just open a new spreadsheet and enter a formula, say =1+5302-3795 to count rows 3795 through 5302, which is more work, but perhaps acceptable if this isn't information you need often.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Counting rows
Try this:
Hit F2 in any cell and select the range in question. This will fill a reference while showing the dimensions in a tooltip. Then hit Escape to undo the edit. Nothing gets selected though
Hit F2 in any cell and select the range in question. This will fill a reference while showing the dimensions in a tooltip. Then hit Escape to undo the edit. Nothing gets selected though
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Counting rows
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Counting rows
The macro fails with unhandled error with any object selection or multiple selection (rectangles or sheets).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Counting rows
Really, I get error message too, when I select multiple ranges... ( ??? I have not any idea to fix it.)
The SACRC macro works for me, (for single selected cell-range,) but i found an another bug: the displayed numbers of rows and columns of the selected range must be increased by one.
The SACRC macro works for me, (for single selected cell-range,) but i found an another bug: the displayed numbers of rows and columns of the selected range must be increased by one.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Counting rows
Still without error handler:
Code: Select all
REM ***** BASIC *****
Option Explicit
Sub SAC
'calls: getSelectedRanges
dim oDoc as object
dim oView as object
'dim oSheet as object
dim oSel as object
dim oAddr as object
dim nSCol as long
dim nSRow as long
dim nERow as long
dim nECol as long
dim MySum as double
dim MyAverage as double
dim MyCount as double
dim MyRowC as LONG
dim MyColC as LONG
dim oRange as object
dim svc as Object
dim e
oDoc = ThisComponent
oView = oDoc.getCurrentController()
oSel = getSelectedRanges(oView)
e = oSel.createEnumeration()
while e.hasMoreElements()
oRange = e.nextElement
oAddr = oRange.getRangeAddress()
nSCol = oAddr.StartColumn
nSRow = oAddr.StartRow
nECol = oAddr.EndColumn
nERow = oAddr.EndRow
MyRowC= MyRowC +nERow - nSRow +1
MyColC= MyColC +nECol - nSCol +1
wend
MySum = oSel.computeFunction(com.sun.star.sheet.GeneralFunction.SUM)
MyCount = oSel.computeFunction(com.sun.star.sheet.GeneralFunction.COUNT)
MyAverage = oSel.computeFunction(com.sun.star.sheet.GeneralFunction.AVERAGE)
MsgBox("Sum = " & MySum & Chr(13) & "Average = " & MyAverage & Chr(13) & "Count = " & MyCount & Chr(13) & "Row count = " & MyRowC & Chr(13) & "Col count = " & MyColC, 64, "SAC : ")
end sub
Function getSelectedRanges(oController)
REM calls: getActiveCell
Dim oSelect
REM return a collection of ranges, regardless of the actual selection
oSelect = oController.getSelection()
If oSelect.supportsService("com.sun.star.sheet.SheetCellRanges") then
getSelectedRanges = oSelect
elseif oSelect.supportsService("com.sun.star.sheet.SheetCellRange") then
REM convert single range to collection of one range:
getSelectedRanges = oSelect.queryIntersection(oSelect.getRangeAddress())
else
REM collection of active cell
oSelect = getActiveCell(oController)
getSelectedRanges = oSelect.queryIntersection(oSelect.getRangeAddress())
endif
End Function
'-------usefull helper-function, returning focussed cell
'by UROS > http://www.oooforum.org/forum/viewtopic.phtml?t=19348
REM 2006-08-09: fixed error when row > 8191
' ;sh; ;lSheet +3
'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253/8191/0/0/0/0/2/246/0/0/8158;0/0/0/0/0/0/2/0/0/0/0
'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253+8192+0+0+0+0+2+246+0+0+8158;0/0/0/0/0/0/2/0/0/0/0
Function getActiveCell(oView)
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
as1() = Split(oView.ViewData, ";")
lSheet = CLng(as1(1))
sDum = as1(lSheet +3)
as1() = Split(sDum, "/")
on error goto errSlash
lCol = CLng(as1(0))
lRow = CLng(as1(1))
on error goto 0
getActiveCell = oView.Model.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
exit Function
errSlash:
if NOT(bErr) then
bErr = True
as1() = Split(sDum, "+")
resume
endif
End Function
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Counting rows
This is a nice technique, perhaps the easiest way, however:Villeroy wrote:Hit F2 in any cell and select the range in question. This will fill a reference while showing the dimensions in a tooltip. Then hit Escape to undo the edit.
- After pressing F2, we need to indicate this is to be a formula by typing an "=". Or alternatively, skip F2 and just type an = sign.
- The technique will not work if the spreadsheet is opened read-only or if the cell is protected.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Counting rows
Thank you, Villeroy for completing and repairing the code.
But, the
I used a "workaround" condition test:
When I selected some multiplied ranges in same column(s) (for example: A1:B3 and A11:B12), then I selected Five rows. It is simple, clear result.
But: How many columns was I selected? Two or Four? Your example function will give the answer: Four. Maybe this is the exact answer, but in my opinion it's a little strange result.
But, the
gives error message for me on an empty cellrange. I think, there is it because, the average function works: AVERAGE = SUM/COUNT, and on an empty range the COUNT equals 0.MyAverage = oSel.computeFunction(com.sun.star.sheet.GeneralFunction.AVERAGE)
I used a "workaround" condition test:
And a theoretic question:if MyCount=0 then MyAverage=0 else MyAverage = svc.callFunction("AVERAGE", Array(oRange))
When I selected some multiplied ranges in same column(s) (for example: A1:B3 and A11:B12), then I selected Five rows. It is simple, clear result.
But: How many columns was I selected? Two or Four? Your example function will give the answer: Four. Maybe this is the exact answer, but in my opinion it's a little strange result.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Counting rows
Indeed, the average function raises an error when you do not check for zero.
My simple approach adds up all the rows and columns of separate ranges in the ranges collection even when they are on the same sheet. I'm not sure how to count SheetX.A1:A9 and SheetX.A11:A19 as one column.
Apart from that, the API treats a selection of
A1:A4;A5:C8
as
A1:B8;C4:C8
Columns have a higher precedence.
Multiple ranges are tricky.
My simple approach adds up all the rows and columns of separate ranges in the ranges collection even when they are on the same sheet. I'm not sure how to count SheetX.A1:A9 and SheetX.A11:A19 as one column.
Apart from that, the API treats a selection of
A1:A4;A5:C8
as
A1:B8;C4:C8
Columns have a higher precedence.
Multiple ranges are tricky.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice