First time here? Check out the FAQ!
asked 2012-08-17 02:58:03 +0200
I have a cellrange and I'm trying to get the number of cells populated in that range.
oCellRange = rr_sheet.getCellRangeByName("a63:b68")
I tried count=oCellRange.computeFunction(com.sun.star.sheet.GeneralFunction.COUNT), but that gives the total number of rows in the range, I just want the ones that have data.
I tried the above call with COUNTA, but that complained and said it was not found. I could write a little function that loops through the DataArray, but I was trying to avoid that.
Sub testCountPopulatedCells Dim oSheets As Variant Dim oSheet As Variant Dim oCellRange As Variant Dim nCount As Long oSheets = ThisComponent.getSheets() oSheet = oSheets.getByIndex(0) oCellRange = oSheet.getCellRangeByName("A63:B68") nCount = getCountNonEmpt(oCellRange) REM # See below... Print "nCount = " + nCount End Sub Function getCountNonEmpt(oRange As Variant) Dim oQry As Variant Dim oCells As Variant Dim oEnum As Variant Dim cnt As Long REM # Here you can combine any of the flags from REM # http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/CellFlags.html oQry = oRange.queryContentCells(com.sun.star.sheet.CellFlags.VALUE _ +com.sun.star.sheet.CellFlags.DATETIME _ +com.sun.star.sheet.CellFlags.STRING _ +com.sun.star.sheet.CellFlags.FORMULA _ +com.sun.star.sheet.CellFlags.OBJECTS) oCells = oQry.getCells() oEnum = oCells.createEnumeration() cnt = 0 Do while oEnum.hasMoreElements() cnt = cnt + 1 oEnum.nextElement() Loop getCountNonEmpt = cnt End Function
LibreOffice is made available by volunteers around the globe, backed by a charitable Foundation. Please support our efforts: Your donation helps us to deliver a better product!
Asked: 2012-08-17 02:58:03 +0200
Seen: 1,067 times
Last updated: Feb 20