Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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) ' 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

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