{"id":12193,"date":"2014-04-28T14:03:20","date_gmt":"2014-04-28T14:03:20","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/wp\/?p=3519"},"modified":"2017-07-18T07:10:13","modified_gmt":"2017-07-18T07:10:13","slug":"collapse-expand-outlines","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/","title":{"rendered":"Excel VBA Tip &#8211; Expand \/ Collapse all the Groups in Excel"},"content":{"rendered":"<p>Dear Readers,<\/p>\n<p>This  is a small VBA tip to automate to expand or collapse groups created in Excel. Before I jump to VBA code about Expanding or collapsing the groups in excel, I would like to brief you about <strong>Grouping or Outlining Functionality<\/strong> in Excel.<br \/>\nIt is always good to know <strong>&#8220;How to do it manually. <\/strong> before automating any step.<\/p>\n<h1>What is Grouping in Excel?<\/h1>\n<p>Most of you who use excel in their day-to-day work, know about grouping. I will explain you about grouping feature of Excel for those who does not know about this. As the name itself suggests, this is the inbuilt feature in Excel which allows users to group their Rows or Columns accordingly. Grouping can be done at Rows and Columns level both. This is called as Outlining as well.<\/p>\n<h1>Steps to do Grouping\/Outlining in Excel?<\/h1>\n<h2>Step 1. <\/h2>\n<p>Select your Rows or Columns where you want to apply the Grouping (Outlining).<\/p>\n<h2>Step 2. <\/h2>\n<p>Go to &#8220;Data&#8221; Tab in Excel Ribbon and Click on Group Button as shown in the below image:<\/p>\n<div align=center>\n<img decoding=\"async\" src=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/04\/Group-Excel-Ribbon.bmp\" alt=\"Outline-Excel-Ribbon\" title=\"Outline-Excel-Ribbon\" class=\"aligncenter size-full wp-image-3521\" \/><\/div>\n<p>&nbsp;<\/p>\n<p>When certain Rows or Columns are grouped then user will be able to see a <strong>+ (Plus) <\/strong> sign to expand all the rows or columns grouped together. Once all the rows or columns are expanded then user will be able to see a <strong>&#8211; (Minus) <\/strong> sign to collapse them as shown in the below picture.<br \/>\n&nbsp;<br \/>\n<img decoding=\"async\" src=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/04\/Level-Expansion.bmp\" alt=\"Different - Outline - Levels\" title=\"Different - Outline - Levels\" class=\"aligncenter size-full wp-image-3524\" \/><br \/>\n&nbsp;<\/p>\n<h1>Is it possible to have nested Grouping?<\/h1>\n<p>Now what is <strong>nested grouping? <\/strong>As Nested means Nested grouping is Grouping within a Group. I will explain this by giving an example. Suppose you have grouped Rows 5 to 20. Within this group, create another sub-group of rows 8 to 12. Same case is with Column grouping as well. Each nesting is called as Level. <strong>Sub-grouping can be done up to 8 levels in Microsoft Excel.<\/strong> Refer the below picture. There are maximum of 8 Levels present. You will not be allowed to group any further level.<br \/>\n&nbsp;<br \/>\n<img decoding=\"async\" src=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/04\/Group-Row-Column1.bmp\" alt=\"Outlining Row and Column\" title=\"Outlining Row and Column\" class=\"aligncenter size-full wp-image-3522\" \/><br \/>\n&nbsp;<\/p>\n<h1>VBA Method used to Display Outline Levels<\/h1>\n<p><strong>Outline.ShowLevels<\/strong> is a method which is used to show outlines of different levels. This method takes RowLevel and ColumnLevel as input.<\/p>\n<blockquote>\n<h2>.Outline.ShowLevels(<i>RowLevels, ColumnLevels<\/i>)<\/h2>\n<h2> Where:<\/h2>\n<h3>RowLevels:<\/h3>\n<p>This is the Row Level number <strong>UP TO<\/strong> which you want to show. It means if you pass this number as 7 then this method will show all the levels till 7. It will not expand the 8th Level but all the levels before 7.<\/p>\n<h3>ColumnLevels<\/h3>\n<p>This is the Column Level number <strong>UP TO<\/strong> which you want to show. It means if you pass this number as 7 then this method will show all the Column levels till 7. It will not expand the 8th Level but all the levels before 7.\n<\/p><\/blockquote>\n<h1>VBA Code to Expand all Outline Levels<\/h1>\n<p>As mentioned earlier there can be a maximum of 8th Level in Row and Column grouping. Therefore to expand all the levels available in a sheet you can pass the maximum possible level number i.e. 8 for both Row and Column Level numbers.<\/p>\n<pre><code class=\"language-vbnet\">\r\nSub Expand_All()\r\n    ActiveSheet.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8\r\nEnd Sub\r\n<\/code><\/pre>\n<h1>VBA Code to Collapse all Outline Levels<\/h1>\n<p>There is only one method <strong>.ShowLevels<\/strong> is available. There is no method for hiding all the levels. But if you understand clearly the meaning of parameters passed as <strong>RowLevels<\/strong> and <strong>ColumnLevels<\/strong> then you will be able to achieve this as well using the same method.<br \/>\n&nbsp;<br \/>\nAs mentioned earlier <strong>LevelNumber<\/strong> are the level number to display all the levels <strong>UP TO<\/strong> that level<strong> ONLY<\/strong>. It means all the levels beyond that will not be shown. Hence if I pass Row and Column Parameter as <strong>1<\/strong> then only first Level will be displayed and all other levels will be hidden and that is how we achieved both the goals <strong>(Expand All &#038; Collapse All)<\/strong> with the same method. <\/p>\n<pre><code class=\"language-vbnet\">\r\nSub Collapse_All()\r\n    ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1\r\nEnd Sub\r\n<\/code><\/pre>\n<blockquote><p>If you want any specific level to be shown in Row or Column levels, you can achieve it by changing the parameters value.<\/p><\/blockquote>\n<span class=\"et_bloom_bottom_trigger\"><\/span>","protected":false},"excerpt":{"rendered":"<p>Dear Readers, This is a small VBA tip to automate to expand or collapse groups created in Excel. Before I jump to VBA code about Expanding or collapsing the groups in excel, I would like to brief you about Grouping or Outlining Functionality in Excel. It is always good to know &#8220;How to do it [&hellip;]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1246,1676],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.9 (Yoast SEO v21.9.1) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Excel VBA to Expand Collapse groping or outlining in Excel - LearnExcelMacro.com<\/title>\n<meta name=\"description\" content=\"What is Outlining in Excel? What is Grouping in Excel? What is outlining in Excel. VBA code to Outline. VBA code to Group in Excel. VBA button to expand all grouping\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel VBA Tip - Expand \/ Collapse all the Groups in Excel\" \/>\n<meta property=\"og:description\" content=\"What is Outlining in Excel? What is Grouping in Excel? What is outlining in Excel. VBA code to Outline. VBA code to Group in Excel. VBA button to expand all grouping\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/\" \/>\n<meta property=\"og:site_name\" content=\"Let&#039;s excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2014-04-28T14:03:20+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-07-18T07:10:13+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/learnexcelmacro.com\/wp\/wp-content\/uploads\/sites\/11\/2014\/04\/Group-Excel-Ribbon.bmp\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Excel VBA Tip &#8211; Expand \/ Collapse all the Groups in Excel\",\"datePublished\":\"2014-04-28T14:03:20+00:00\",\"dateModified\":\"2017-07-18T07:10:13+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/\"},\"wordCount\":649,\"commentCount\":7,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"Excel Macro\",\"Excel Tips\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/\",\"name\":\"Excel VBA to Expand Collapse groping or outlining in Excel - LearnExcelMacro.com\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2014-04-28T14:03:20+00:00\",\"dateModified\":\"2017-07-18T07:10:13+00:00\",\"description\":\"What is Outlining in Excel? What is Grouping in Excel? What is outlining in Excel. VBA code to Outline. VBA code to Group in Excel. VBA button to expand all grouping\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Macro\",\"item\":\"https:\/\/vmlogger.com\/excel\/macro\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Excel VBA Tip &#8211; Expand \/ Collapse all the Groups in Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let&#039;s excel in Excel\",\"description\":\"Let&#039;s share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore &amp; currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Excel VBA to Expand Collapse groping or outlining in Excel - LearnExcelMacro.com","description":"What is Outlining in Excel? What is Grouping in Excel? What is outlining in Excel. VBA code to Outline. VBA code to Group in Excel. VBA button to expand all grouping","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/","og_locale":"en_US","og_type":"article","og_title":"Excel VBA Tip - Expand \/ Collapse all the Groups in Excel","og_description":"What is Outlining in Excel? What is Grouping in Excel? What is outlining in Excel. VBA code to Outline. VBA code to Group in Excel. VBA button to expand all grouping","og_url":"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/","og_site_name":"Let&#039;s excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2014-04-28T14:03:20+00:00","article_modified_time":"2017-07-18T07:10:13+00:00","og_image":[{"url":"http:\/\/learnexcelmacro.com\/wp\/wp-content\/uploads\/sites\/11\/2014\/04\/Group-Excel-Ribbon.bmp"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Excel VBA Tip &#8211; Expand \/ Collapse all the Groups in Excel","datePublished":"2014-04-28T14:03:20+00:00","dateModified":"2017-07-18T07:10:13+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/"},"wordCount":649,"commentCount":7,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["Excel Macro","Excel Tips"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/","url":"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/","name":"Excel VBA to Expand Collapse groping or outlining in Excel - LearnExcelMacro.com","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2014-04-28T14:03:20+00:00","dateModified":"2017-07-18T07:10:13+00:00","description":"What is Outlining in Excel? What is Grouping in Excel? What is outlining in Excel. VBA code to Outline. VBA code to Group in Excel. VBA button to expand all grouping","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2014\/04\/collapse-expand-outlines\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Macro","item":"https:\/\/vmlogger.com\/excel\/macro\/"},{"@type":"ListItem","position":3,"name":"Excel VBA Tip &#8211; Expand \/ Collapse all the Groups in Excel"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let&#039;s excel in Excel","description":"Let&#039;s share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore &amp; currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12193"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=12193"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12193\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12193"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12193"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12193"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}