VBA Merge Cells and Unmerge cells in Excel – VBA Tutorials

Introduction/Problem Statement

In this blog, we will talk about different ways of VBA merge cells or unmerge cells using VBA. In most of cases user do it manually because “Merge” & “Unmerge” buttons shows in the “Home” tab of the ribbon but sometimes user writes a program where they need to VBA merge cells and unmerge cells, in this case herein below we have shown some examples of the code which will make the job easier of the users.

Solution

VBA Merge cells:

This code will merge cells B1:B4.

Merge Cells
  • Save

Merge Row

We can merge Entire rows by specifying the row number that we want to merge. This code will merge rows 2 to 6.

Merge Row
  • Save

Merge Columns

We can merge entire columns by specifying the columns letters of the that we want to merge. This code will merge columns A:D

Merge Columns
  • Save

Merge and Center Cell Contents Horizontally

This code will merge the range B1:B4 and set the alignment of contents cell to center.

Merge and Center Cell Contents Horizontally
  • Save

Merge and Center Cell Contents Vertically

This code will merge the range A1:A4 and set the alignment of contents cell to center.

Merge and Center Cell Contents Vertically
  • Save

Unmerge Cells

We can unmerge cells by using the unmerge method of range. The specified range must be the part of merge range

This code will unmerge all cells between the range of H1 to H30(H1 To H30 is the part of merged cells).so we will define any range between H1 To H30 in the range object of the VBA code(as we have mention H21 in our code).

Unmerge Cells
  • Save

Unmerge all merge cells in Activesheet

If the active sheet has multiple merge range, then we can use this code

Unmerge all merge cells in Activesheet
  • Save

Conclusion

If you want to VBA merge cells or unmerge cells,  use any code shown above (as per user need).

Leave a Reply

Your email address will not be published. Required fields are marked *

Copy link
Powered by Social Snap