Remove blank field in Pivot table

[dothuha123]

Hi everyody,
pls help me with my issue
I created a pivot table and refresh it automatically by macros
the source data of pivot table is update auto too, and have more info each week.
the problem is the blank field (the whole column) always appears in my PV table and make things difficult with my auto file.
i already try to uncheck the blank field in PV table filter, and it disappear. but after it gone, the new data of the next update week gone too.
please see the following example:

-WHEN UNABLE BLANK FIELD IN PIVOT FILTER:


<table border="0" cellpadding="0" cellspacing="0" width="542"><colgroup><col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="width:48pt" span="6" width="64"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:60pt" align="left" height="20" width="80">Row Labels</td> <td class="xl63" style="border-left:none;width:48pt" align="left" width="64">week 1</td> <td class="xl63" style="border-left:none;width:48pt" align="left" width="64">week 2</td> <td class="xl63" style="border-left:none;width:48pt" align="left" width="64">week 3</td> <td class="xl63" style="border-left:none;width:48pt" align="left" width="64">(blank)</td> <td class="xl63" style="border-left:none;width:48pt" align="left" width="64">week 4</td> <td class="xl63" style="border-left:none;width:48pt" align="left" width="64">week 5</td> <td class="xl63" style="border-left:none;width:59pt" align="left" width="78">Grand Total</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="left" height="20">a</td> <td class="xl63" style="border-top:none;border-left:none" align="right">189</td> <td class="xl63" style="border-top:none;border-left:none" align="right">159.5</td> <td class="xl63" style="border-top:none;border-left:none" align="right">171.5</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl63" style="border-top:none;border-left:none" align="right">159.5</td> <td class="xl63" style="border-top:none;border-left:none" align="right">171.5</td> <td class="xl63" style="border-top:none;border-left:none" align="right">851</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="left" height="20">b</td> <td class="xl63" style="border-top:none;border-left:none" align="right">6</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl63" style="border-top:none;border-left:none" align="right">6</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="left" height="20">c</td> <td class="xl63" style="border-top:none;border-left:none" align="right">183</td> <td class="xl63" style="border-top:none;border-left:none" align="right">159.5</td> <td class="xl63" style="border-top:none;border-left:none" align="right">171.5</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl63" style="border-top:none;border-left:none" align="right">159.5</td> <td class="xl63" style="border-top:none;border-left:none" align="right">171.5</td> <td class="xl63" style="border-top:none;border-left:none" align="right">845</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="left" height="20">d</td> <td class="xl63" style="border-top:none;border-left:none" align="right">244</td> <td class="xl63" style="border-top:none;border-left:none" align="right">285</td> <td class="xl63" style="border-top:none;border-left:none" align="right">270</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl63" style="border-top:none;border-left:none" align="right">285</td> <td class="xl63" style="border-top:none;border-left:none" align="right">270</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1354</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="left" height="20">e</td> <td class="xl63" style="border-top:none;border-left:none" align="right">244</td> <td class="xl63" style="border-top:none;border-left:none" align="right">285</td> <td class="xl63" style="border-top:none;border-left:none" align="right">270</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl63" style="border-top:none;border-left:none" align="right">285</td> <td class="xl63" style="border-top:none;border-left:none" align="right">270</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1354</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="left" height="20">f</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl63" style="border-top:none;border-left:none" align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="left" height="20">Grand Total</td> <td class="xl63" style="border-top:none;border-left:none" align="right">3399.5</td> <td class="xl63" style="border-top:none;border-left:none" align="right">3100.75</td> <td class="xl63" style="border-top:none;border-left:none" align="right">3515.75</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl63" style="border-top:none;border-left:none" align="right">3100.75</td> <td class="xl63" style="border-top:none;border-left:none" align="right">3515.75</td> <td class="xl63" style="border-top:none;border-left:none" align="right">16632.5</td> </tr> </tbody></table>

_WHEN DISABLE BLANK FIELD:

<table border="0" cellpadding="0" cellspacing="0" width="350"><colgroup><col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="width:48pt" span="3" width="64"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:60pt" align="left" height="20" width="80">Row Labels</td> <td class="xl65" style="border-left:none;width:48pt" align="left" width="64">week 1</td> <td class="xl65" style="border-left:none;width:48pt" align="left" width="64">week 2</td> <td class="xl65" style="border-left:none;width:48pt" align="left" width="64">week 3</td> <td class="xl65" style="border-left:none;width:59pt" align="left" width="78">Grand Total</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" align="left" height="20">a</td> <td class="xl65" style="border-top:none;border-left:none" align="right">189</td> <td class="xl65" style="border-top:none;border-left:none" align="right">159.5</td> <td class="xl65" style="border-top:none;border-left:none" align="right">171.5</td> <td class="xl65" style="border-top:none;border-left:none" align="right">520</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" align="left" height="20">b</td> <td class="xl65" style="border-top:none;border-left:none" align="right">6</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">6</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" align="left" height="20">c</td> <td class="xl65" style="border-top:none;border-left:none" align="right">183</td> <td class="xl65" style="border-top:none;border-left:none" align="right">159.5</td> <td class="xl65" style="border-top:none;border-left:none" align="right">171.5</td> <td class="xl65" style="border-top:none;border-left:none" align="right">514</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" align="left" height="20">d</td> <td class="xl65" style="border-top:none;border-left:none" align="right">244</td> <td class="xl65" style="border-top:none;border-left:none" align="right">285</td> <td class="xl65" style="border-top:none;border-left:none" align="right">270</td> <td class="xl65" style="border-top:none;border-left:none" align="right">799</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" align="left" height="20">e</td> <td class="xl65" style="border-top:none;border-left:none" align="right">244</td> <td class="xl65" style="border-top:none;border-left:none" align="right">285</td> <td class="xl65" style="border-top:none;border-left:none" align="right">270</td> <td class="xl65" style="border-top:none;border-left:none" align="right">799</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" align="left" height="20">f</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" align="left" height="20">Grand Total</td> <td class="xl65" style="border-top:none;border-left:none" align="right">3399.5</td> <td class="xl65" style="border-top:none;border-left:none" align="right">3100.75</td> <td class="xl65" style="border-top:none;border-left:none" align="right">3515.75</td> <td class="xl65" style="border-top:none;border-left:none" align="right">10016

</td> </tr> </tbody></table>
So when I remove the blank column, all data update later gone too. so the file can not be updated automatically. i want to remove the blank column forever and dont need to touch in the PV each time i update it.

i really appreciate if you can help me with my problem.

thanks

<table style="width: 1px; height: 160px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="width:48pt" span="4" width="64"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="border-left:none;width:48pt" align="left" width="64">
</td> <td class="xl65" style="border-left:none;width:48pt" align="left" width="64">
</td> <td class="xl65" style="border-left:none;width:48pt" align="left" width="64">
</td> <td class="xl65" style="border-left:none;width:48pt" align="left" width="64">
</td> <td class="xl65" style="border-left:none;width:59pt" align="left" width="78">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="border-top:none;border-left:none" align="right">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="border-top:none;border-left:none" align="right">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="border-top:none;border-left:none" align="right">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">
</td></tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="border-top:none;border-left:none" align="right">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">
</td> </tr> </tbody></table>

 

[cyrilbrd]

Hi,

(blank) should show at the end, however could you please post a sample of how your row data is arranged?

You can use HMTL wrap to post a sample.

if:

this	when	what
a	week1	0.665439328
b	week1	0.710857249
c	week1	0.657383038
d	week1	0.462368834
e	week1	0.110110833
f	week1	0.753979798
a	week2	0.104854001
b	week2	0.778896362
c	week2	0.415565597
d	week2	0.594204315
e	week2	0.906218788
f	week2	0.572402372
a	week3	0.344520023
b	week3	0.114564634
c	week3	0.483415889
d	week3	0.299224008
e	week3	0.279568806
f	week3	0.025412823
a		
b		
c		
d		
e		
f		
a	week4	0.748486816
b	week4	0.077641795
c	week4	0.570263393
d	week4	0.056734701
e	week4	0.744408578
f	week4	0.568821932

then pivot:

Sum of what	Column Labels				
Row Labels	week1	week2	week3	week4	Grand Total
a	0.67	0.10	0.34	0.75	1.86
b	0.71	0.78	0.11	0.08	1.68
c	0.66	0.42	0.48	0.57	2.13
d	0.46	0.59	0.30	0.06	1.41
e	0.11	0.91	0.28	0.74	2.04
f	0.75	0.57	0.03	0.57	1.92
Grand Total	3.36	3.37	1.55	2.77	11.05

 

[dothuha123]

Hi Cyrilbrd,
thank you very much for you reply.
i dont really know what is a HTML wrap but i wanna make clear my example.
i have an automatically updated file that changes by week. for ex, last week I have data:
<table border="0" cellpadding="0" cellspacing="0" width="309"><colgroup><col style="mso-width-source:userset;mso-width-alt:4096;width:84pt" width="112"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> <col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:84pt" height="20" width="112"> Annee Semaine </td> <td style="width:71pt" width="95">Num Fonction</td> <td style="width:77pt" width="102">TEMPS_POINTE</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201219</td> <td align="right">1100</td> <td align="right">8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201219</td> <td align="right">2100</td> <td align="right">4</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201220</td> <td align="right">2100</td> <td align="right">7</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201220</td> <td align="right">2100</td> <td align="right">3</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201221</td> <td align="right">2100</td> <td align="right">4</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201221</td> <td align="right">1100</td> <td align="right">3</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201221</td> <td align="right">2100</td> <td align="right">8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201221</td> <td align="right">2100</td> <td align="right">2</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201222</td> <td align="right">1100</td> <td align="right">8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201222</td> <td align="right">2100</td> <td align="right">8</td> </tr> </tbody></table>
and pivot:

<table border="0" cellpadding="0" cellspacing="0" width="548"><colgroup><col style="mso-width-source:userset;mso-width-alt:5485;width:113pt" width="150"> <col style="width:48pt" span="5" width="64"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:113pt" height="20" width="150">Sum of TEMPS_POINTE</td> <td class="xl65" style="border-left:none;width:48pt" width="64">Column Labels</td> <td class="xl65" style="border-left:none;width:48pt" width="64">
</td> <td class="xl65" style="border-left:none;width:48pt" width="64">
</td> <td class="xl65" style="border-left:none;width:48pt" width="64">
</td> <td class="xl65" style="border-left:none;width:48pt" width="64">
</td> <td class="xl65" style="border-left:none;width:59pt" width="78">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">Row Labels</td> <td class="xl65" style="border-top:none;border-left:none" align="right">201219</td> <td class="xl65" style="border-top:none;border-left:none" align="right">201220</td> <td class="xl65" style="border-top:none;border-left:none" align="right">201221</td> <td class="xl65" style="border-top:none;border-left:none" align="right">201222</td> <td class="xl65" style="border-top:none;border-left:none">(blank)</td> <td class="xl65" style="border-top:none;border-left:none">Grand Total</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" align="right" height="20">1100</td> <td class="xl65" style="border-top:none;border-left:none" align="right">8</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">3</td> <td class="xl65" style="border-top:none;border-left:none" align="right">8</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">19</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" align="right" height="20">2100</td> <td class="xl65" style="border-top:none;border-left:none" align="right">4</td> <td class="xl65" style="border-top:none;border-left:none" align="right">10</td> <td class="xl65" style="border-top:none;border-left:none" align="right">14</td> <td class="xl65" style="border-top:none;border-left:none" align="right">8</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">36</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">(blank)</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">Grand Total</td> <td class="xl65" style="border-top:none;border-left:none" align="right">12</td> <td class="xl65" style="border-top:none;border-left:none" align="right">10</td> <td class="xl65" style="border-top:none;border-left:none" align="right">17</td> <td class="xl65" style="border-top:none;border-left:none" align="right">16</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">55</td> </tr> </tbody></table>


this week these are more data in the system that came in my file:

<table border="0" cellpadding="0" cellspacing="0" width="309"><colgroup><col style="mso-width-source:userset;mso-width-alt:4096;width:84pt" width="112"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> <col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:84pt" height="20" width="112"> Annee Semaine </td> <td style="width:71pt" width="95">Num Fonction</td> <td style="width:77pt" width="102">TEMPS_POINTE</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201219</td> <td align="right">1100</td> <td align="right">8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201219</td> <td align="right">2100</td> <td align="right">4</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201220</td> <td align="right">2100</td> <td align="right">7</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201220</td> <td align="right">2100</td> <td align="right">3</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201221</td> <td align="right">2100</td> <td align="right">4</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201221</td> <td align="right">1100</td> <td align="right">3</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201221</td> <td align="right">2100</td> <td align="right">8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201221</td> <td align="right">2100</td> <td align="right">2</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201222</td> <td align="right">1100</td> <td align="right">8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201222</td> <td align="right">2100</td> <td align="right">8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201223</td> <td align="right">2100</td> <td align="right">7.75</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201223</td> <td align="right">2100</td> <td align="right">7.75</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201223</td> <td align="right">200</td> <td align="right">4</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201223</td> <td align="right">2100</td> <td align="right">8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201223</td> <td align="right">2100</td> <td align="right">8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201223</td> <td align="right">2100</td> <td align="right">7.75</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201223</td> <td align="right">200</td> <td align="right">8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201223</td> <td align="right">2100</td> <td align="right">8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">201223</td> <td align="right">2100</td> <td align="right">7.75</td> </tr> </tbody></table>
and i refresh the PV table by macro, i have:

<table border="0" cellpadding="0" cellspacing="0" width="512"><colgroup><col style="width:48pt" span="8" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" height="20" width="64">Sum of TEMPS_POINTE</td> <td colspan="2" style="mso-ignore:colspan;width:96pt" width="128">Column Lwabels</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row Labels</td> <td align="right">201219</td> <td align="right">201220</td> <td align="right">201221</td> <td align="right">201222</td> <td>(blank)</td> <td align="right">201223</td> <td>Grand Total</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1100</td> <td align="right">8</td> <td>
</td> <td align="right">3</td> <td align="right">8</td> <td>
</td> <td>
</td> <td align="right">19</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">2100</td> <td align="right">4</td> <td align="right">10</td> <td align="right">14</td> <td align="right">8</td> <td>
</td> <td align="right">55</td> <td align="right">91</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">(blank)</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">200</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">12</td> <td align="right">12</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Grand Total</td> <td align="right">12</td> <td align="right">10</td> <td align="right">17</td> <td align="right">16</td> <td>
</td> <td align="right">67</td> <td align="right">122</td> </tr> </tbody></table>
the blank column and row interrupt my data cause i have other macros use the PV data in another place. i want the blank to disappear. but if i uncheck the (blank) in column filter, and after auto refresh the PV, i just have this:

<table border="0" cellpadding="0" cellspacing="0" width="548"><colgroup><col style="mso-width-source:userset;mso-width-alt:5485;width:113pt" width="150"> <col style="width:48pt" span="5" width="64"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:113pt" height="20" width="150">Sum of TEMPS_POINTE</td> <td class="xl65" style="border-left:none;width:48pt" width="64">Column Labels</td> <td class="xl65" style="border-left:none;width:48pt" width="64">
</td> <td class="xl65" style="border-left:none;width:48pt" width="64">
</td> <td class="xl65" style="border-left:none;width:48pt" width="64">
</td> <td class="xl65" style="border-left:none;width:48pt" width="64">
</td> <td class="xl65" style="border-left:none;width:59pt" width="78">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">Row Labels</td> <td class="xl65" style="border-top:none;border-left:none" align="right">201219</td> <td class="xl65" style="border-top:none;border-left:none" align="right">201220</td> <td class="xl65" style="border-top:none;border-left:none" align="right">201221</td> <td class="xl65" style="border-top:none;border-left:none" align="right">201222</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">Grand Total</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" align="right" height="20">1100</td> <td class="xl65" style="border-top:none;border-left:none" align="right">8</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">3</td> <td class="xl65" style="border-top:none;border-left:none" align="right">8</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">19</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" align="right" height="20">2100</td> <td class="xl65" style="border-top:none;border-left:none" align="right">4</td> <td class="xl65" style="border-top:none;border-left:none" align="right">10</td> <td class="xl65" style="border-top:none;border-left:none" align="right">14</td> <td class="xl65" style="border-top:none;border-left:none" align="right">8</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">36</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">Grand Total</td> <td class="xl65" style="border-top:none;border-left:none" align="right">12</td> <td class="xl65" style="border-top:none;border-left:none" align="right">10</td> <td class="xl65" style="border-top:none;border-left:none" align="right">17</td> <td class="xl65" style="border-top:none;border-left:none" align="right">16</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">55</td> </tr> </tbody></table>
it means new data gone with the blank too.
is there any way for me to remove the blank without touching in the PV in each time of updating. with this problem, the file can not be really an auto file.

you can try with the sample data, pivot, then add more data after, then refresh the PV. you can see

thanks again for your help.

 

[cyrilbrd]

would you agree to the assumption that the vba select more than the effective range of raw data resulting into blanks?
blanks do NOT do well in pivot (rows or Columns) that is why you end up with a data separation in your Rows labels.

Please try (just to confirm): select only the range of data A1 to C24 per example containing data. Your pivot should behave correctly and display all info. Right?

 

[dothuha123]

It is true that when we select the correct area (A1 to C24 for example), the PV displays perfect information.

And it is ok to use VBA to select the variable range of data, too.

but the problem is that: how can i use macro to ask a certain Pivot table to change its source data? is PV support to do that automatically?
because i can not change data source of PV, I have to select a wide range of data at the beginning (the whole columns of A, B, C) and click on Refresh in each time i have new more data. and the blank appear to interrupt my work.

creating a new PV with the new data range for each time of updating is not a good idea, i think.

thank you very much cyrilbrd, it is nice to talk with you

 

[Jerry Sullivan]

Hi dothuha123,

Consider using a Dynamic Named Range for your PivotTable DataSource.

The Contextures Site has some good instructions on how to do this.

http://www.contextures.com/xlPivot01.html

 

[dothuha123]

Thank you very much Cyrilbrd and JS411
that is exact what I want


I really appreciate your help

 

[cyrilbrd]

you are most welcome.