Ask Your Question

count cells in a range in macro [closed]

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

anonymous user


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(, 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


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

JohnSUN gravatar imageJohnSUN ( 2012-08-17 08:45:30 +0200 )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 +0200 )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 +0200 )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 +0200 )edit

1 Answer

Sort by » oldest newest most voted

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

JohnSUN gravatar image

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

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 
    oQry = oRange.queryContentCells( _
    oCells = oQry.getCells()
    oEnum = oCells.createEnumeration()
    cnt = 0
    Do while oEnum.hasMoreElements()
        cnt = cnt + 1
    getCountNonEmpt = cnt
End Function
edit flag offensive delete link more

Question Tools


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

Seen: 8,012 times

Last updated: Feb 20 '13