How to unmerge specific cells in Excel using VBA

How to unmerge specific cells in Excel using VBA

Sometimes you just want to unmerge cells which contain some specific value but you want to do it in one go rather than doing it one by one. You can automate this process using Excel VBA.

Let’s assume an excel sheet contains some merged cells but values in those merged cells are different and you want to only unmerge those cells which contain a value “Hello” in it.

Here is the solution to unmerge specific cells in Excel using VBA

VBA Code

Sub UnMergeCell()
'Talentnett Solutions - visit us at https://talentnett.com' 
Dim cell As Range 
For Each cell In ThisWorkbook.ActiveSheet.UsedRange
    If cell.Value = "Hello" Then 'Change the value as per you requirement' 	
      cell.MergeCells = False 
    End If 
  	Next 
End Sub

#Step 1 – Go to Excel

#Step 2 – Right-click on the sheet tab>>View code or Press “ALT+F11”

#Step 3 – Click on Insert>>Module

#Step 4– Paste the above code in the VBA window and Press “Alt+Q”

paste macro code here

#Step 5 – Save your excel file as a macro-enabled file (Press “F12” >> change Save Type as “Excel Macro-Enabled Workbook”)

#Step 6 – Go to Blank sheet >> Insert any shape – Insert>>Shapes

#Step 7 – Right-click on that shape and Assign that macro [UnMergeCell]

#Step 8 – Click on the shape

Cells which contain a specific value will be Unmerged!

Read More:

How to send email notifications on Google Spreadsheets if any cell value changes

How to convert multiple text values to a date format using a single formula

COMMENTS