Display data labels on stacked bar pivot chart ONLY for values greater than 0

Hi, guys,

Would you please help?

I have a stacked bar pivot chart, which only displays the values greater than 0, but I don't want any labels from values equal to 0 showing up and making the chart basically unreadable. I just need the labels from values greater than 0, but getting them automatically with VBA code (macro).

The way I´m doing this is referencing the seriescollection "points" which need to have labels applied. In another worksheet ("referencia") I basically copy the information from the pivot table from all 31 data fiels. Then, range D3:D33 contains the data field number if the value is greater than 0.

Here's my code:

Please Login or Register  to view this content.

When I run this, I get runtime error '1004': Method 'SeriesCollection' of object '_Chart' failed. I think it has to do with my using the s variable on:

Please Login or Register  to view this content.

, since when I change it to a number, it works fine.

I hope I made sense, but it's late here and I'm desperate.

Thank you,
José

What do you mean?

Thank you,
José

Hi,

The variable "s" is for the series point I need the label for.

See, I have 31 columns on the pivot table, each of which have different values. All of these columns are in the pivot chart, but most have values equal to 0, so they're not displayed. I want to be able to automatically display labels for only all column values greater than 0.

What I've done is reference the values in the pivot table on other cells, and if the value is greater than 0, the code has "s" equal to it, so that the "activechart.seriescollection(s).applydatalabels" can work.

Thank you,
José