Query in VBA code for pivot table count distinct


I am using excel 2013 and facing issue to when I am trying to get distinct count of a column in pivot table. below is my code but it is not working. As normally If want to apply distinct count then while inserting pivot table I have to check on “Add this data to the Data Model”

-‘set the cache(datasource) of Pivot table


Set cacheofpt = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range(“a1:s” & lastrow), Version:=xlPivotTableVersion15)


‘create pivot table


Set pt = ActiveSheet.PivotTables.Add(cacheofpt, Range(“A1”), “Raw Data”, DefaultVersion:=xlPivotTableVersion15)


‘Puts field in

With pt

    ‘add the fiels

    .PivotFields(“hrelation_updated”).Orientation = xlColumnField

    .PivotFields(“lFeedback2”).Orientation = xlRowField

    With .PivotFields(“respid”)

    .Orientation = xlDataField

    .Function = xlDistinctCount

    End With

End With


Please let me know why this is not working. Any help will be appreciated!!..

By: Kirti

3,856 thoughts on “Query in VBA code for pivot table count distinct

  1. udfauzi

    Having read this I thought it was extremely informative. I appreciate you taking the time and effort to put this informative article together. I once again find myself personally spending way too much time both reading and posting comments. But so what, it was still worthwhile!

  2. hiking

    I’d like to thank you for the efforts you’ve put in writing this blog. I’m hoping to see the same high-grade content by you in the future as well. In truth, your creative writing abilities has motivated me to get my very own website now 😉

  3. genolyze

    Aw, this was an incredibly nice post. Finding the time and actual effort to produce a really good article… but what can I say… I procrastinate a whole lot and never seem to get nearly anything done.

  4. Acompanhantes campinas

    Great blog! Do you have any tips and hints for aspiring writers?
    I’m planning to start my own site soon but I’m a little lost on everything.
    Would you propose starting with a free platform like WordPress or
    go for a paid option? There are so many choices out there that I’m completely overwhelmed ..
    Any tips? Many thanks!

Comments are closed.