If you've worked with merged cells you may know that they can cause issues with things like copying and pasting, sorting, and counting cells.
Merged cells can cause VBA to fall over too, so it is best to avoid them. Using Center Across Selection is a better option.
CTRL+1 is the shortcut to open Format Cells -> Alignment and choose Center Across Selection from the Horizontal dropdown list.
I've written a couple of simple VBA routines that will highlight any merged cells on the sheet, and you can run the other macro to unmerge any merged cells.
Download The Sample Workbook
Enter your email address below to download the sample workbook.
Let's say we have a sheet like this that is used to create invoices.
Running my routine highlights any merged cells which shows this
We can then remove any merged cells and with a little bit of reformatting we end up with this invoice - and no merged cells in sight.
The Code
The first macro searches the active sheet for merged cells and highlights them in green.
I've assigned the shortcut sequence CTRL+SHIFT+M to the macro and running it repeatedly toggles the highlighting on/off.
If you want to unmerge the merged cells then press CTRL+SHIFT+U to run this macro
Static Variables in VBA
YD Forums
I’m need to add a step to the process. After finding and unmerging cells, I want to fill the now empty cells with the contents of the previously merged cell. For example, if A1, A2, and A3 were merged and contained TEXT, I want to unmerge into individual A1, A2, and A3, and have each of them contain TEXT.
Can you advise how to do that?
Thanks
Catalin Bombea
Hi,
If there are merged cells, you can read the address of the merged area and use it to fill it after unmerge:
Sub UnMergeAndFill()
Dim cell As Range, MergeAddress As String
For Each cell In ActiveSheet.UsedRange
If cell.MergeCells = True Then
MergeAddress = cell.MergeArea.Address
Range(MergeAddress).UnMerge
Range(MergeAddress).Value = Range(MergeAddress).Cells(1).Value
End If
Next
End Sub
When you unmerge cells, the value always goes into the first cell of the merged area, that’s what the code speculates.
John Mann
I’ve never understood why one would want to merge cells in a spreadsheet. I do use that ability sometimes in wordprocessor tables, but they have a different function. I long ago recorded macro in my personal workbook to perform the Centre Across Selection format, which I use quite often. It’s triggered by an icon in my QAT (which stretches almost 1/2 way across the screen on my laptop.
Sub CentreAcrossCollumns()
‘
‘ CentreAcrossCollumns Macro
‘
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.MergeCells = False
End With
End Sub
Catalin Bombea
Hi John,
If we try to understand everything that has no apparent reason, a lifetime will not be enough 🙂
Thanks for sharing
Catalin
Rudra Sharma
Very^99 simple code, exactly I had thought.
jim
and don’t forget the final step
find out who merged them in the first place and “re-educate” them
jim
slawek
It will be nice if use proper collection in for each loop instead of default object. Many you readers can be confused what this loop actually does.
mrsimlaoui
nice post. thank you.
i have a suggestion without vba
1- select the sheet
2- ctrl+F
3- Format
4- in the list we choose format then alignement
5- i activate the box befor merged cell
6- serach all
7- ctrl+A to select all cells
8- change format (maybe add a color…)
9- correct cell one by one
thank you. but your way is better and faster.
cheers.
Philip Treacy
Thanks. There’s more than one way to skin a cat 🙂