CODEWITHSUNDEEP
excelvba
subbu
subbu

Reputation: 3299

Sum of column's values after filtering in another column excel vba

I have set filters on field 20 ,9 and 15

With dsWrksheet.Range("T1")
    .AutoFilter Field:=20, Criteria1:="=*" & Id & "*"
    .AutoFilter Field:=9, Criteria1:="Controls Solutions"
    .AutoFilter Field:=15, Criteria1:="India"
End With

After the filter is set I am calculating the sum of values in visible cells of column AB like this

Set myRange = dsWrksheet.Range("AB1:AB" & dsWrksheet.Range("AB" & Rows.count).End(xlUp).Row)
voucherValInd = WorksheetFunction.Sum(myRange.SpecialCells(xlCellTypeVisible))

The problem is arising when the cells are empty I am getting wrong values as sum.

Help is appreciated.

Upvotes: 0

Views: 475

Answers (1)

BigBen
BigBen

Reputation: 49998

I think WorksheetFunction.Subtotal is easier here. 109 means SUM, excluding hidden rows, as detailed here. Plus, you can just use it on the entire column instead of finding the last row:

voucherValInd = WorksheetFunction.Subtotal(109, dsWrksheet.Range("AB:AB"))

Upvotes: 1

Related Questions