How Do I Quickly Un-merge cells with VBA?

I shudder at the sight of merged cells in a spreadsheet.

Sure, they may make Excel scorecards look neat and pretty… They also make them a pain to maintain since merged cells interfere with sorting and pasting as values

There’s nothing more annoying than being unable to paste as values on top of cells that had been merged.

This essentially means that if you need to paste data as values (as you often do when refreshing scorecards, whilst trying to keep your file size at a minimum), you must go through all merged cells and unmerge them.

Trust me, I have had to do this a couple of times and it was not an experience I would like to repeat… So I got myself a handy VBA snippet to relieve me of this mind-numbing refresh prep exercise.

Best of all – it is a pretty generic one, so you can also incorporate it in your code!

Sub UnmergeCells()
Dim Cell As Range
'written by Angelina Teneva, September 2016
For Each Cell In ActiveSheet.Range("A2:AS4100")
If Cell.MergeArea.Address <> Cell.Address Then Cell.UnMerge
Next Cell
End Sub

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.