Ask Your Question
1

count cells in a range in macro [closed]

asked 2012-08-17 02:58:03 +0100

anonymous user

Anonymous

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-22 16:19:40.661076

Comments

May be cellsCount = oCellRange.getRows().getCount() * oCellRange.getColumns().getCount() Why not?

JohnSUN gravatar imageJohnSUN ( 2012-08-17 08:45:30 +0100 )edit

the getCount() does the same thing, it counts all of the rows in the range, not just the ones that are occupied.

ptn4egl gravatar imageptn4egl ( 2012-08-20 00:37:09 +0100 )edit

Oh, excuse me! I did not notice the word "populated"! :-( Difficult to publish the code in the comments, I'll publish it in a reply

JohnSUN gravatar imageJohnSUN ( 2012-08-20 09:36:23 +0100 )edit

no worries. Yea, I ended up writing a little function to return the count of populated cells. Thanks, mine looks very similar to yours.

ptn4egl gravatar imageptn4egl ( 2012-08-21 00:45:58 +0100 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2012-08-20 09:37:37 +0100

JohnSUN gravatar image

updated 2013-02-20 11:15:37 +0100

Try this:

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
edit flag offensive delete link more

Question Tools

Stats

Asked: 2012-08-17 02:58:03 +0100

Seen: 7,842 times

Last updated: Feb 20 '13