Remove blank field in Pivot table

dothuha123

New Member
Joined
Jun 5, 2012
Messages
10
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>
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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:
HTML:
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:

HTML:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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 ;)
 
Upvote 0

Forum statistics

Threads
1,211,867
Messages
6,104,460
Members
447,910
Latest member
abrook1308

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top