Pivot Table: How to code a Special grouping using VBA

[hcova]

Dear sirs:
I have the following pivot table:

[TABLE="width: 425"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Sum of Q2017[/TD]
[TD]Sum of Q2018[/TD]
[TD]Diff_Q[/TD]
[TD]%Share[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD="align: right"]5265[/TD]
[TD="align: right"]4639[/TD]
[TD="align: right"]-626[/TD]
[TD="align: right"]38%[/TD]
[/TR]
[TR]
[TD] Boston[/TD]
[TD="align: right"]2290[/TD]
[TD="align: right"]1994[/TD]
[TD="align: right"]-296[/TD]
[TD="align: right"]47%[/TD]
[/TR]
[TR]
[TD] Philadelphia[/TD]
[TD="align: right"]1045[/TD]
[TD="align: right"]879[/TD]
[TD="align: right"]-166[/TD]
[TD="align: right"]27%[/TD]
[/TR]
[TR]
[TD] New York[/TD]
[TD="align: right"]1811[/TD]
[TD="align: right"]1709[/TD]
[TD="align: right"]-102[/TD]
[TD="align: right"]16%[/TD]
[/TR]
[TR]
[TD] Miami[/TD]
[TD="align: right"]119[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]-62[/TD]
[TD="align: right"]10%[/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD="align: right"]1620[/TD]
[TD="align: right"]612[/TD]
[TD="align: right"]-1008[/TD]
[TD="align: right"]62%[/TD]
[/TR]
[TR]
[TD] Seattle[/TD]
[TD="align: right"]437[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]-382[/TD]
[TD="align: right"]38%[/TD]
[/TR]
[TR]
[TD] Portland[/TD]
[TD="align: right"]444[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]-337[/TD]
[TD="align: right"]33%[/TD]
[/TR]
[TR]
[TD] Las Vegas[/TD]
[TD="align: right"]237[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]-155[/TD]
[TD="align: right"]15%[/TD]
[/TR]
[TR]
[TD] San Diego[/TD]
[TD="align: right"]422[/TD]
[TD="align: right"]338[/TD]
[TD="align: right"]-84[/TD]
[TD="align: right"]8%[/TD]
[/TR]
[TR]
[TD] San Francisco[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]-50[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD]Total general[/TD]
[TD="align: right"]6885[/TD]
[TD="align: right"]5251[/TD]
[TD="align: right"]-1634[/TD]
[TD="align: right"]100%[/TD]
[/TR]
</tbody>[/TABLE]

I need to code in VBA a macro that allow me to group the cities according the %share column. In the following way:

[TABLE="width: 400"]
<tbody>[TR]
[TD="class: xl78, width: 80"]Row Labels[/TD]
[TD="class: xl70, width: 80"]Sum Q2017[/TD]
[TD="class: xl70, width: 80"]Sum Q2018[/TD]
[TD="class: xl71, width: 80"] Diff_Q[/TD]
[TD="class: xl71, width: 80"] %Share[/TD]
[/TR]
[TR]
[TD="class: xl72"]East[/TD]
[TD="class: xl73, align: right"]5265[/TD]
[TD="class: xl73, align: right"]4639[/TD]
[TD="class: xl73, align: right"]-626[/TD]
[TD="class: xl74, align: right"]38%[/TD]
[/TR]
[TR]
[TD="class: xl67"] Boston[/TD]
[TD="class: xl68, align: right"]2290[/TD]
[TD="class: xl68, align: right"]1994[/TD]
[TD="class: xl68, align: right"]-296[/TD]
[TD="class: xl69, align: right"]47%[/TD]
[/TR]
[TR]
[TD="class: xl67"] Philadelphia[/TD]
[TD="class: xl68, align: right"]1045[/TD]
[TD="class: xl68, align: right"]879[/TD]
[TD="class: xl68, align: right"]-166[/TD]
[TD="class: xl69, align: right"]27%[/TD]
[/TR]
[TR]
[TD="class: xl79"] Rest[/TD]
[TD="class: xl80, align: right"]1930[/TD]
[TD="class: xl80, align: right"]1766[/TD]
[TD="class: xl80, align: right"]-164[/TD]
[TD="class: xl81, align: right"]26%[/TD]
[/TR]
[TR]
[TD="class: xl72"]West[/TD]
[TD="class: xl73, align: right"]1620[/TD]
[TD="class: xl73, align: right"]612[/TD]
[TD="class: xl73, align: right"]-1008[/TD]
[TD="class: xl74, align: right"]62%[/TD]
[/TR]
[TR]
[TD="class: xl67"] Seattle[/TD]
[TD="class: xl68, align: right"]437[/TD]
[TD="class: xl68, align: right"]55[/TD]
[TD="class: xl68, align: right"]-382[/TD]
[TD="class: xl69, align: right"]38%[/TD]
[/TR]
[TR]
[TD="class: xl67"] Portland[/TD]
[TD="class: xl68, align: right"]444[/TD]
[TD="class: xl68, align: right"]107[/TD]
[TD="class: xl68, align: right"]-337[/TD]
[TD="class: xl69, align: right"]33%[/TD]
[/TR]
[TR]
[TD="class: xl79"] Rest[/TD]
[TD="class: xl80, align: right"]739[/TD]
[TD="class: xl80, align: right"]450[/TD]
[TD="class: xl80, align: right"]-289[/TD]
[TD="class: xl81, align: right"]29%[/TD]
[/TR]
[TR]
[TD="class: xl75"]Total general[/TD]
[TD="class: xl76, align: right"]6885[/TD]
[TD="class: xl76, align: right"]5251[/TD]
[TD="class: xl76, align: right"]-1634[/TD]
[TD="class: xl77, align: right"]100%[/TD]
[/TR]
</tbody>[/TABLE]

As you can see what I need is create a group called for “Rest” that includes (add) all cities whose %share value be under 25%. Moreover each subtotal row must keep the same previous value.
In advance, thanks a lot.
Best regards
Hernan Covarrubias
h.covarru@gmail.com

PS: If anyone needs the worksheet I can send it.

 

[HolyExcel]

using the group function in the pivto table is not sufficient?