Sum column of filtered data with macro?

[sokolowitzky]

Dear All,

I have found this code and modified a bit.
But now I need to add numbers in the column, upto the the end of block of filtered data.

Code: Select all

REM  *****  BASIC  *****
sub filterandrank
strSourceRange = "b2:b30000"
strTargetCell = "E1"
mains = 1
filterDistinct(mains, strSourceRange, strTargetCell)
END SUB

FUNCTION filterDistinct(mains As String, strSourceRange As String, strTargetCell As String, Optional bContainsHeader As Boolean, Optional bCaseSensitive As Boolean )
REM Uses a Filter to copy distinct rows from the specified Source Range into a new Range that starts from the specified Target Cell.
REM <strSourceRange>    : specifies the Range to find distinct rows in, e.g. "A1:B99".
REM <strTargetCell>     : specifies the Cell to put the first found distinct row in, e.g. "D1".
REM <bContainsHeader>   : OPTIONAL - pass TRUE if the Source Range contains a Header.
REM <bCaseSensitive>    : OPTIONAL - pass TRUE if case matters while searching for distinct rows.
    Dim oSheet As Object, oSourceRange As Object, oFilter As Object
    oSheet = ThisComponent.Sheets.getbyindex(mains)
    oSourceRange = oSheet.getCellRangebyName(strSourceRange )
    oFilter = oSourceRange.createFilterDescriptor( True )
    oFilter.SkipDuplicates = True
    Dim oFilterFields(0) as new com.sun.star.sheet.TableFilterField
    oFilterFields(0).Operator = com.sun.star.sheet.FilterOperator.NOT_EMPTY
    oFilter.setFilterFields(oFilterFields) 
    srow = oSheet.getCellRangebyName(strTargetCell).CellAddress.row
    scolumn = oSheet.getCellRangebyName(strTargetCell).CellAddress.column
    frow = srow+12345
    scell = oSheet.getCellRangebyName(strTargetCell)
    scell.string = ""
    
oTargetRange = oSheet.getcellrangebyposition(3, srow, 4, 1999)
oTargetRange.fillSeries(com.sun.star.sheet.FillDirection.TO_BOTTOM, com.sun.star.sheet.FillMode.SIMPLE,0,0,0)


    oFilter.CopyOutputData = True
    oFilter.OutputPosition = oSheet.getCellRangebyName(strTargetCell).CellAddress
    If Not IsMissing( bContainsHeader ) Then oFilter.ContainsHeader = bContainsHeader
    If Not IsMissing( bCaseSensitive ) Then oFilter.IsCaseSensitive = bCaseSensitive
    oSourceRange.filter( oFilter )     
        for i = 0 to 6
    tissel = oSheet.getcellbyposition(3, i)
    tissel.value = i
    next
End FUNCTION

[Zizi64]

But now I need to add numbers in the column, upto the the end of block of filtered data.

Code: Select all

 REM  *****  BASIC  *****
    sub filterandrank
    strSourceRange = "b2:b30000"

"

What is the task really?

- Do you want to add numbers by your macro?

- or do you want get the used range dinamically (instead of the permanent range b2:b30000) for the properly working of the macro on the inreased area?

[sokolowitzky]

I want to get numbers dinamically according to number of rows of filtered data. I can make basic write numbers upto last used row. But this code is so long. I thought may be I can get these numbers with a formula similar to this one.

Code: Select all

for i = 0 to oFilter.Endrow

Can I do this?

[Zizi64]

I posted this code today to this topic: viewtopic.php?f=20&t=95064&p=454212#p454212

Code: Select all

    REM  *****  BASIC  *****

    Function LastRowOfUsedArea(lSheetNumber as long, optional oDoc as object) as long
    rem The sheet numbers are Zero based values in the macros.

    Dim oSheet as object
    Dim   oDocument as object
    Dim oCursor as object

       rem Here is a sample for the handling of the "optional passed paramater":
       If IsMissing( oDoc ) Then
          oDocument = ThisComponent
       else oDocument = oDoc
       EndIf
       rem End of checking of the optional parameter. The function got the passed document  or "this" (the actual) one.
       
       oSheet = oDocument.Sheets(lSheetNumber)
       oCursor = oSheet.createCursor
       oCursor.gotoEndOfUsedArea(False)

    LastRowOfUsedArea = oCursor.RangeAddress.EndRow

    End function


    Function LastColumnOfUsedArea(lSheetNumber as long, optional oDoc as object) as long
    rem The sheet numbers are Zero based values in the macros.

    Dim oSheet as object
    Dim   oDocument as object
    Dim oCursor as object

       rem Here is a sample for the handling of the  "optional passed paramater":
       If IsMissing( oDoc ) Then
          oDocument = ThisComponent
       else oDocument = oDoc
       EndIf
       rem End of checking of the optional parameter. The function got the passed document  or "this" (the actual) one.
       
       oSheet = oDocument.Sheets(lSheetNumber)
       oCursor = oSheet.createCursor
       oCursor.gotoEndOfUsedArea(False)

    LastColumnOfUsedArea = oCursor.RangeAddress.EndColumn

    End function

[MrProgrammer]

But now I need to add numbers in the column, upto the the end of block of filtered data.

Does SUBTOTAL do what you need, with no evil macro?

201809221049.ods

(12.18 KiB) Downloaded 138 times

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.