In Excel macro, using the range or selection object, merge cells & unmerge operations can be done.
Nothing much to explain in this.
But just remember that: once the cells are merged, the cell can be reference with the first address.
For example: If you merge cells A1:B1 , then you can assign a value to the merged cell by using Range (“A1”) object.
Excel VBA to Merge Cells
Use this VBA code to Merge cells right from your macro code. This is not a difficult to code to make.
You can also use Record macro option to get this code snippet.
Sub Merge_Cells() 'Code to Merge Excel Cells Range("G5:H5").Merge End Sub Sub Merge_Range(iRng As Range) iRng.Merge End Sub
Once you press F5, the cells within the range will be merged as one cell.
The same can be used as below.
Unmerge cells in Excel VBA
Just use the keyword unmerge with Range object.
Sub unMerge_Cells() 'Code to Merge Excel Cells Range("G5:H5").UnMerge End Sub Sub unMerge_Range(iRng As Range) iRng.UnMerge End Sub
Keep the cursor inside the first function and press F5. Excel will unmerge the range given as input.
Using Selection Object to Merge & Unmerge
Instead of using the Range object, if we are not sure on which cells need to be merged, but just want to merge all cells in selection.
Sub Sel_Merge_Cells() 'Code to Merge Excel Cells Range("G5:H5").Select Selection.merge End Sub Sub Sel_unMerge_Cells() 'Code to Merge Excel Cells Selection.UnMerge End Sub
In the code, to illustrate which cells are selected, we have explicitly mentioned the range.selection.
In your case, if the cells are already selected, then just use ‘Selection.merge’.