{"id":11305,"date":"2019-12-27T16:55:03","date_gmt":"2019-12-27T15:55:03","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=11305"},"modified":"2024-02-06T16:16:59","modified_gmt":"2024-02-06T15:16:59","slug":"hide-pivot-table-subtotals-using-macros-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/hide-pivot-table-subtotals-using-macros-in-excel\/","title":{"rendered":"Hide Pivot Table Subtotals Using Macros In Excel"},"content":{"rendered":"<p>Excel Macros encompass a lot of functionality, and modifying <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-pivot-tables\/\" target=\"_blank\" rel=\"noopener\" title=\"Pivot Tables\" data-wpil-keyword-link=\"linked\">Pivot Tables<\/a> is one of them! Let us <strong>hide <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-pivot-tables\/\" target=\"_blank\" rel=\"noopener\" title=\"pivot table\" data-wpil-keyword-link=\"linked\">pivot table<\/a> subtotals<\/strong> using Excel Macros!<\/p>\n<p>Make sure your Excel has the Developer Tab enabled following\u00a0<a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/how-to-create-your-first-macro-button-in-excel-beginners-tutorial\/\" target=\"_blank\" rel=\"noopener noreferrer\">this tutorial<\/a>.<\/p>\n<p>I explain how you can do this below step by step!<\/p>\n<p><em><strong>What does it do?<\/strong><\/em><\/p>\n<p>Hide the Pivot Table subtotals<\/p>\n<p><em><strong>Copy Source Code:<\/strong><\/em><\/p>\n<pre class=\"brush: vb; title: ; notranslate\" title=\"\">\r\n\r\n'Select a cell first from your pivot table\r\nSub HidePivotTableSubtotals()\r\n\r\nDim pTable As PivotTable\r\nDim pField As PivotField\r\n\r\nOn Error Resume Next\r\n\r\n'Get the pivot table first\r\nSet pTable = ActiveSheet.PivotTables(ActiveCell.PivotTable.name)\r\n\r\n'Check if a pivot table is found\r\nIf pTable Is Nothing Then\r\nMsgBox &amp;quot;Please select a cell first from your Pivot Table.&amp;quot;\r\nExit Sub\r\nEnd If\r\n\r\n'For each subtotal, make it hidden\r\nFor Each pField In pTable.PivotFields\r\npField.Subtotals(1) = True\r\npField.Subtotals(1) = False\r\nNext pField\r\n\r\nEnd Sub\r\n\r\n<\/pre>\n<p><em><strong>Final Result:\u00a0<\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Hide Pivot Table Subtotals Using Macros In Excel\"  class=\"aligncenter size-full wp-image-12046\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-04.jpg\" alt=\"Hide Pivot Table Subtotals Using Macros In Excel\" width=\"289\" height=\"414\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-04.jpg 289w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-04-209x300.jpg 209w\" sizes=\"(max-width: 289px) 100vw, 289px\" \/><\/p>\n<p><em><strong>Exercise Workbook:<\/strong><\/em><\/p>\n<div class=\"after-post-box\">\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/AsCAXwg-utI?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle;\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><\/p>\n<p class=\"line-two\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals.xlsm\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals.xlsm\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Hide-Pivot-Table-Subtotals.xlsm<\/span><\/a><\/p>\n<p class=\"line-two\">\n<\/div>\n<hr \/>\n<p>This is our Pivot Table and we want to hide these subtotals:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Hide Pivot Table Subtotals Using Macros In Excel\"  class=\"aligncenter size-full wp-image-12043\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-01.jpg\" alt=\"Hide Pivot Table Subtotals Using Macros In Excel\" width=\"294\" height=\"480\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-01.jpg 294w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-01-184x300.jpg 184w\" sizes=\"(max-width: 294px) 100vw, 294px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong><\/span>\u00a0Go to\u00a0<em><strong>Developer &gt; Code &gt; Visual Basic<\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-10906 aligncenter\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/01\/How-to-Insert-Multiple-Columns-01.jpg\" alt=\"\" width=\"816\" height=\"139\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/01\/How-to-Insert-Multiple-Columns-01.jpg 816w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/01\/How-to-Insert-Multiple-Columns-01-300x51.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/01\/How-to-Insert-Multiple-Columns-01-768x131.jpg 768w\" sizes=\"(max-width: 816px) 100vw, 816px\" title=\"\"><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span>\u00a0Paste in your code and\u00a0<strong>Select Save<\/strong>. Close the window afterwards.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Hide Pivot Table Subtotals Using Macros In Excel\"  class=\"aligncenter size-full wp-image-12044\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-02.jpg\" alt=\"Hide Pivot Table Subtotals Using Macros In Excel\" width=\"1389\" height=\"593\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-02.jpg 1389w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-02-300x128.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-02-1024x437.jpg 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-02-768x328.jpg 768w\" sizes=\"(max-width: 1389px) 100vw, 1389px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:\u00a0<\/strong><\/span>Let us test it out!<\/p>\n<p>Open the sheet containing the data. Go to\u00a0<em><strong>Developer &gt; Code &gt; <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-macros-examples\/\" target=\"_blank\" rel=\"noopener\" title=\"Macros\" data-wpil-keyword-link=\"linked\">Macros<\/a><\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11245\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Unmerge-Cells-02.jpg\" alt=\"\" width=\"854\" height=\"142\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Unmerge-Cells-02.jpg 854w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Unmerge-Cells-02-300x50.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Unmerge-Cells-02-768x128.jpg 768w\" sizes=\"(max-width: 854px) 100vw, 854px\" title=\"\"><\/p>\n<p>&nbsp;<\/p>\n<p>Make sure your pivot table and <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-macros-examples\/\" target=\"_blank\" rel=\"noopener\" title=\"macro\" data-wpil-keyword-link=\"linked\">macro<\/a> are selected. Click <strong>Run<\/strong>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Hide Pivot Table Subtotals Using Macros In Excel\"  class=\"aligncenter size-full wp-image-12045\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-03.jpg\" alt=\"Hide Pivot Table Subtotals Using Macros In Excel\" width=\"935\" height=\"594\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-03.jpg 935w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-03-300x191.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-03-768x488.jpg 768w\" sizes=\"(max-width: 935px) 100vw, 935px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>With just one click, <strong>your Pivot Table subtotals are now hidden<\/strong>!<\/p>\n<p><span style=\"color: #0000ff;\"><strong>\u00a0<img loading=\"lazy\" decoding=\"async\" title=\"Hide Pivot Table Subtotals Using Macros In Excel\"  class=\"aligncenter size-full wp-image-12046\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-04.jpg\" alt=\"Hide Pivot Table Subtotals Using Macros In Excel\" width=\"289\" height=\"414\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-04.jpg 289w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Hide-Pivot-Table-Subtotals-04-209x300.jpg 209w\" sizes=\"(max-width: 289px) 100vw, 289px\" \/><\/strong><\/span><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #0000ff;\"><strong>How to Hide Pivot Table Subtotals Using Macros In Excel<\/strong><\/span><\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-66.html\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-13266\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/10\/101-Macros-Book.png\" alt=\"101 Macros Book\" width=\"1200\" height=\"628\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/10\/101-Macros-Book.png 1200w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/10\/101-Macros-Book-300x157.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/10\/101-Macros-Book-1024x536.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/10\/101-Macros-Book-768x402.png 768w\" sizes=\"(max-width: 1200px) 100vw, 1200px\" title=\"\"><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Excel Macros encompass a lot of functionality, and modifying Pivot Tables is one of them! Let us hide pivot table subtotals using Excel Macros! Make sure your Excel has the Developer Tab enabled following\u00a0this tutorial. I explain how you can do this below step by step! What does it do? Hide the Pivot Table subtotals [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":18081,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[56,643],"tags":[106],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/11305"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=11305"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/11305\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/18081"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=11305"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=11305"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=11305"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}