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.Excel VBA Merge CellsVisual Basic12345678Sub Merge_Cells()'Code to Merge Excel CellsRange("G5:H5").MergeEnd SubSub Merge_Range(iRng As Range)iRng.MergeEnd 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.Excel vba Unmerge cellsVisual Basic12345678Sub unMerge_Cells()'Code to Merge Excel CellsRange("G5:H5").UnMergeEnd SubSub unMerge_Range(iRng As Range)iRng.UnMergeEnd 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.Excel VBA merge selected cellsVisual Basic123456789Sub Sel_Merge_Cells()'Code to Merge Excel CellsRange("G5:H5").SelectSelection.mergeEnd SubSub Sel_unMerge_Cells()'Code to Merge Excel CellsSelection.UnMergeEnd 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’.
Excel VBA – Merge Cells – Unmerge Range