{"id":16775,"date":"2023-11-08T08:35:53","date_gmt":"2023-11-08T08:35:53","guid":{"rendered":"https:\/\/officetuts.net\/excel\/?p=16775"},"modified":"2024-02-19T15:00:00","modified_gmt":"2024-02-19T15:00:00","slug":"collapse-columns-in-excel-using-vba","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/","title":{"rendered":"How to Collapse Columns in Excel Using VBA"},"content":{"rendered":"\n<p>Manipulating columns in Excel may often be necessary when dealing with large spreadsheets to maintain readability and organization. One such manipulation is collapsing or hiding columns using Visual Basic for Applications (VBA). This is particularly useful when you want to control the visibility of certain data within your workbook programmatically. Here&#8217;s how to collapse columns in Excel using VBA.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Steps<\/h2>\n\n\n\n<p>Before we dive into the VBA macro to collapse columns, it&#8217;s essential that we have a dataset to work with. For this example, let&#8217;s assume we have a spreadsheet with data in columns A through F, and we want to collapse columns C through E.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Determine the Columns to Collapse<\/h3>\n\n\n\n<p>First, you need to identify which columns you want to hide. Excel columns are referenced by letters, so if you want to collapse columns C to E, you know that you&#8217;ll be working with columns 3 to 5 internally in VBA since column A is column 1, B is 2, and so on.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"496\" height=\"260\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/collapse-columns-in-excel-using-vba-example.png\" alt=\"\" class=\"wp-image-16781\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/collapse-columns-in-excel-using-vba-example.png 496w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/collapse-columns-in-excel-using-vba-example-300x157.png 300w\" sizes=\"(max-width: 496px) 100vw, 496px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Open the VBA Editor<\/h3>\n\n\n\n<p>To access the VBA editor within Excel, you can press <code>Alt<\/code> + <code>F11<\/code> on your keyboard. This shortcut works with all versions of Excel. Once in the VBA editor, you can create a new module where you will place your macro code.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Write the VBA Macro<\/h3>\n\n\n\n<p>To collapse the desired columns, you can create a simple macro as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Sub CollapseColumns()\n    Columns(\"C:E\").EntireColumn.Hidden = True\nEnd Sub<\/code><\/pre>\n\n\n\n<p>This macro uses the <code>Columns<\/code> object to reference columns C through E directly and then sets the <code>Hidden<\/code> property to <code>True<\/code> to collapse them.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Execute the Macro<\/h3>\n\n\n\n<p>You can run your macro either by pressing <code>F5<\/code> while in the VBA editor with the macro selected or by assigning the macro to a button in your Excel worksheet for easier access.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"296\" height=\"262\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/collapse-columns-in-excel-using-vba-result.png\" alt=\"\" class=\"wp-image-16782\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p>Collapsing columns in Excel using VBA can greatly enhance your ability to manage spreadsheet visibility and improve the user experience. With the provided VBA script, you can programmatically hide specific columns, making your Excel sheets cleaner and more presentable.<\/p>\n\n\n\n<p>Remember, VBA is a powerful tool that can perform various actions beyond just collapsing columns, so feel free to explore and experiment with VBA macros to automate your Excel tasks.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Manipulating columns in Excel may often be necessary when dealing with large spreadsheets to maintain readability and organization. One such manipulation is collapsing&#8230;<\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to Collapse Columns in Excel Using VBA<\/title>\n<meta name=\"description\" content=\"Looking to hide or collapse columns in Excel? Learn how to use VBA to control the visibility of certain data within your workbook.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Collapse Columns in Excel Using VBA\" \/>\n<meta property=\"og:description\" content=\"Looking to hide or collapse columns in Excel? Learn how to use VBA to control the visibility of certain data within your workbook.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/\" \/>\n<meta property=\"article:published_time\" content=\"2023-11-08T08:35:53+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-02-19T15:00:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/collapse-columns-in-excel-using-vba-example.png\" \/>\n<meta name=\"author\" content=\"Tomasz Decker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tomasz Decker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/10baa1deb54627da5d59757d6924066e\"},\"headline\":\"How to Collapse Columns in Excel Using VBA\",\"datePublished\":\"2023-11-08T08:35:53+00:00\",\"dateModified\":\"2024-02-19T15:00:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/\"},\"wordCount\":357,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/collapse-columns-in-excel-using-vba-example.png\",\"articleSection\":[\"vba\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/\",\"url\":\"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/\",\"name\":\"How to Collapse Columns in Excel Using VBA\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/collapse-columns-in-excel-using-vba-example.png\",\"datePublished\":\"2023-11-08T08:35:53+00:00\",\"dateModified\":\"2024-02-19T15:00:00+00:00\",\"description\":\"Looking to hide or collapse columns in Excel? Learn how to use VBA to control the visibility of certain data within your workbook.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/#primaryimage\",\"url\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/collapse-columns-in-excel-using-vba-example.png\",\"contentUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/collapse-columns-in-excel-using-vba-example.png\",\"width\":496,\"height\":260},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Collapse Columns in Excel Using VBA\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/10baa1deb54627da5d59757d6924066e\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d92ef5a1e35bf0d44baf479313c76713?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d92ef5a1e35bf0d44baf479313c76713?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Collapse Columns in Excel Using VBA","description":"Looking to hide or collapse columns in Excel? Learn how to use VBA to control the visibility of certain data within your workbook.","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:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/","og_locale":"en_US","og_type":"article","og_title":"How to Collapse Columns in Excel Using VBA","og_description":"Looking to hide or collapse columns in Excel? Learn how to use VBA to control the visibility of certain data within your workbook.","og_url":"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/","article_published_time":"2023-11-08T08:35:53+00:00","article_modified_time":"2024-02-19T15:00:00+00:00","og_image":[{"url":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/collapse-columns-in-excel-using-vba-example.png"}],"author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/10baa1deb54627da5d59757d6924066e"},"headline":"How to Collapse Columns in Excel Using VBA","datePublished":"2023-11-08T08:35:53+00:00","dateModified":"2024-02-19T15:00:00+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/"},"wordCount":357,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/collapse-columns-in-excel-using-vba-example.png","articleSection":["vba"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/","url":"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/","name":"How to Collapse Columns in Excel Using VBA","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/collapse-columns-in-excel-using-vba-example.png","datePublished":"2023-11-08T08:35:53+00:00","dateModified":"2024-02-19T15:00:00+00:00","description":"Looking to hide or collapse columns in Excel? Learn how to use VBA to control the visibility of certain data within your workbook.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/#primaryimage","url":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/collapse-columns-in-excel-using-vba-example.png","contentUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/collapse-columns-in-excel-using-vba-example.png","width":496,"height":260},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/vba\/collapse-columns-in-excel-using-vba\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"How to Collapse Columns in Excel Using VBA"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."},{"@type":"Person","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/10baa1deb54627da5d59757d6924066e","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d92ef5a1e35bf0d44baf479313c76713?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d92ef5a1e35bf0d44baf479313c76713?s=96&d=mm&r=g","caption":"Tomasz Decker"}}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/16775"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=16775"}],"version-history":[{"count":2,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/16775\/revisions"}],"predecessor-version":[{"id":16964,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/16775\/revisions\/16964"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=16775"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=16775"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=16775"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}