none
how to make filtered values Sum through VBA to Excel (Some of the rows filtered through VBA) RRS feed

  • General discussion

  • Hi,

    In one Sheet (Input Data), I have entered values from A1:A500, B1:B500, C1:C100, etc..

    Anbu 5 1 12
    Somu 2 2 2
    Anbu 6 5 5
    Somu 8 4 6
    Anbu 9 8 7
    Anbu 5 9 8
    Anbu 10 7 9
    Somu 8 5 5
    Somu 9 6 6
    Somu 6 8 4

    The out put sheet's shows like this, I need Sum value of "Column Error1", Example Total 35

    Project   Name Error 1 Error 2 Error 3
    Anbu 5 1 12
    Anbu 6 5 5
    Anbu 9 8 7
    Anbu 5 9 8
    Anbu 10 7 9

    I need Total               35                                XX                                XXX

    Please provide how to create in VBA

    Thanks in advance.

    Anbu


    • Edited by G Anbu Friday, November 15, 2013 12:12 AM
    Friday, November 15, 2013 12:11 AM

All replies

  • The Subtotal function in Excel ignores hidden rows that result from a list being filtered.
    The "9" tells it to Sum.
      =SUBTOTAL(9,A1:A500)

    In VBA...
       Range("A501").Formula = "=SUBTOTAL(9,A1:A500)"
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 11:41 PM
    Friday, November 15, 2013 3:45 AM