{"id":4218,"date":"2019-04-04T07:07:49","date_gmt":"2019-04-04T07:07:49","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=4218"},"modified":"2024-03-29T15:55:40","modified_gmt":"2024-03-29T15:55:40","slug":"merge-cells-with-the-same-values","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/","title":{"rendered":"Merge Cells With the Same Values"},"content":{"rendered":"\n<div class=\"wp-block-file\"><a id=\"wp-block-file--media-915dd2f5-55e2-4a7b-b1ae-f3a9d4f3d39d\" href=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/12\/Merge-Cells-With-the-Same-Values.xlsx\">Merge-Cells-With-the-Same-Values<\/a><a href=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/12\/Merge-Cells-With-the-Same-Values.xlsx\" class=\"wp-block-file__button wp-element-button\" download aria-describedby=\"wp-block-file--media-915dd2f5-55e2-4a7b-b1ae-f3a9d4f3d39d\">Download File<\/a><\/div>\n\n\n\n<p>If you have a worksheet with the same data in adjacent cells, you may want to merge them for visual reasons. In this tutorial, I will show you how you can do it.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"410\" height=\"160\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/merge-cells-example.png\" alt=\"\" class=\"wp-image-4221\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/merge-cells-example.png 410w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/merge-cells-example-300x117.png 300w\" sizes=\"(max-width: 410px) 100vw, 410px\" \/><\/figure>\n\n\n\n<p>In this case, you can <a href=\"https:\/\/officetuts.net\/excel\/vba\/select-range-using-variables\/\">select ranges<\/a> <strong>A1:A2<\/strong> and press the merge\nbutton, then do the same for <strong>A4:A6<\/strong>. But if you are dealing with hundreds of cells,\nit\u2019s not the best idea.<\/p>\n\n\n\n<p>Of course, the best way here is to use some VBA code to do\nthe work for us.<\/p>\n\n\n\n<p>First, prepare your data.<\/p>\n\n\n\n<p>The <a href=\"https:\/\/officetuts.net\/excel\/examples\/merge-split-cells\/\">cells that you want to merge<\/a> must be sorted, otherwise, only cells that are adjacent and\nhave the same value will be merged.<\/p>\n\n\n\n<p>Your sorted example\nshould look like this.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"218\" height=\"160\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/sorted-example.png\" alt=\"\" class=\"wp-image-4222\"\/><\/figure>\n\n\n\n<p>After you sort the data, you can run the following code.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Sub MergeSimilarCells()\n    Application.DisplayAlerts = False\n    Application.ScreenUpdating = False\n\n    Set myRange = Range(\"A1:A6\")\n\nCheckAgain:\n    For Each cell In myRange\n        If cell.Value = cell.Offset(1, 0).Value And Not IsEmpty(cell) Then\n            Range(cell, cell.Offset(1, 0)).Merge\n            cell.VerticalAlignment = xlCenter\n            GoTo CheckAgain\n        End If\n    Next\n\n    Application.DisplayAlerts = True\n    Application.ScreenUpdating = True\nEnd Sub<\/code><\/pre>\n\n\n\n<p>This is going to give us the following result.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"218\" height=\"160\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/sorted-and-merged.png\" alt=\"\" class=\"wp-image-4223\"\/><\/figure>\n\n\n\n<p><strong>Code explanation:<\/strong><\/p>\n\n\n\n<p>The following line of code disables alerts.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Application.DisplayAlerts = False<\/code><\/pre>\n\n\n\n<p>If you don\u2019t add this line, each time the code tries to <a href=\"https:\/\/officetuts.net\/excel\/training\/merging-cells\/\">merge cells<\/a>, you will get this message.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"449\" height=\"152\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/warning-message.png\" alt=\"\" class=\"wp-image-4224\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/warning-message.png 449w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/warning-message-300x102.png 300w\" sizes=\"(max-width: 449px) 100vw, 449px\" \/><\/figure>\n\n\n\n<p>In our case, <a href=\"https:\/\/officetuts.net\/excel\/examples\/shortcut-keys-for-merge-and-center\/\">Excel will try to merge<\/a> cells three times and will display this warning each time.<\/p>\n\n\n\n<p>The next line of code is used to disable screen updating each time Excel <a href=\"https:\/\/officetuts.net\/excel\/examples\/combine-cells\/\">merge cells<\/a>. This line of code will improve the performance greatly if you have a lot of data. In won\u2019t do much in our case.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Application.ScreenUpdating = False<\/code><\/pre>\n\n\n\n<p>Then, we run the loop\nfor <strong>myRange<\/strong>. This range consists of\na single column, so the next cell will always be the new row.<\/p>\n\n\n\n<p>This line checks whether the cell in the next row is equal to the current cell. It also checks if the cell is not empty, otherwise, the loop would go to the last cell inside the <a href=\"https:\/\/officetuts.net\/excel\/training\/worksheets\/\">worksheets<\/a> and the program would crash.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>If cell.Value = cell.Offset(1, 0).Value And Not IsEmpty(cell)<\/code><\/pre>\n\n\n\n<p>If the condition is met the cells are <a href=\"https:\/\/officetuts.net\/excel\/examples\/merge-and-center-data\/\">merged and centered<\/a> vertically.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Range(cell, cell.Offset(1, 0)).Merge\ncell.VerticalAlignment = xlCenter<\/code><\/pre>\n\n\n\n<p>The next line is the <strong>GoTo<\/strong> statement.<\/p>\n\n\n\n<p>It will move the execution before the loop to check if there\nare more cells to check.<\/p>\n\n\n\n<p>At the end, we are going to set <strong>ScreenUpdating<\/strong> and <strong>DisplayAlterts<\/strong> to <strong>TRUE<\/strong>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you have a worksheet with the same data in adjacent cells, you may want to merge them for visual reasons. In this&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19],"tags":[170,190],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Merge Cells With the Same Values<\/title>\n<meta name=\"description\" content=\"Learn how to merge cells in Excel with the same data in adjacent cells. This tutorial explains how to do it manually and with VBA code. Download the example file included.\" \/>\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\/merge-cells-with-the-same-values\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Merge Cells With the Same Values\" \/>\n<meta property=\"og:description\" content=\"Learn how to merge cells in Excel with the same data in adjacent cells. This tutorial explains how to do it manually and with VBA code. Download the example file included.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-04T07:07:49+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-29T15:55:40+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/merge-cells-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\/merge-cells-with-the-same-values\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"Merge Cells With the Same Values\",\"datePublished\":\"2019-04-04T07:07:49+00:00\",\"dateModified\":\"2024-03-29T15:55:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/\"},\"wordCount\":353,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/merge-cells-example.png\",\"keywords\":[\"file\",\"pinterest\"],\"articleSection\":[\"vba\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/\",\"url\":\"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/\",\"name\":\"Merge Cells With the Same Values\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/merge-cells-example.png\",\"datePublished\":\"2019-04-04T07:07:49+00:00\",\"dateModified\":\"2024-03-29T15:55:40+00:00\",\"description\":\"Learn how to merge cells in Excel with the same data in adjacent cells. This tutorial explains how to do it manually and with VBA code. Download the example file included.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/merge-cells-example.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/merge-cells-example.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Merge Cells With the Same Values\"}]},{\"@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.\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Merge Cells With the Same Values","description":"Learn how to merge cells in Excel with the same data in adjacent cells. This tutorial explains how to do it manually and with VBA code. Download the example file included.","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\/merge-cells-with-the-same-values\/","og_locale":"en_US","og_type":"article","og_title":"Merge Cells With the Same Values","og_description":"Learn how to merge cells in Excel with the same data in adjacent cells. This tutorial explains how to do it manually and with VBA code. Download the example file included.","og_url":"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/","article_published_time":"2019-04-04T07:07:49+00:00","article_modified_time":"2024-03-29T15:55:40+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/merge-cells-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\/merge-cells-with-the-same-values\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"Merge Cells With the Same Values","datePublished":"2019-04-04T07:07:49+00:00","dateModified":"2024-03-29T15:55:40+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/"},"wordCount":353,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/merge-cells-example.png","keywords":["file","pinterest"],"articleSection":["vba"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/","url":"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/","name":"Merge Cells With the Same Values","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/merge-cells-example.png","datePublished":"2019-04-04T07:07:49+00:00","dateModified":"2024-03-29T15:55:40+00:00","description":"Learn how to merge cells in Excel with the same data in adjacent cells. This tutorial explains how to do it manually and with VBA code. Download the example file included.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/merge-cells-example.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/merge-cells-example.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/vba\/merge-cells-with-the-same-values\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Merge Cells With the Same Values"}]},{"@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."}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/4218"}],"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\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=4218"}],"version-history":[{"count":13,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/4218\/revisions"}],"predecessor-version":[{"id":14722,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/4218\/revisions\/14722"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=4218"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=4218"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=4218"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}