Unmerge All Merged Cells In Your Excel Worksheet.


Hello Excellers, and welcome back to another #MacroMonday#Excel tip today. Do you know if there are merged cells in your Excel worksheet?. Do you want to remove or unmerge cells all at once?. Then a tiny piece of Excel VBA macro code will speed up your work in a flash. read on to find how to unmerge cells in Excel.

If you want to read more of my related blog posts on merged cells feel free to read my links below.

 

To Merge Or Not To Merge That is The Question

How To Find All Merged Cells In Your Worksheet

Preparing To Write The Code.

First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. You then have a choice. You can create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save your code in your Personal Macro workbook, it will be available for use in any of my Excel workbooks. If you store it in the current workbook, then use is restricted to that workbook.

As you can see this code will be useful to reuse in any workbook. I will create and save this for future use in my Personal Macro Workbook.

 

Learn More About Your Personal Macro Workbook (PMW)

If you want to read more about your Excel PMW then check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

 

Starting The Macro

We need to start the macro by inserting a new module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it Sort_Worksheets. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We need to enter the rest of the code between these two lines.

Sub UnMerge_All_Cells

End sub

UnMerging All Merged Cells.

This routine is a really simple one. I did say it was. It is a one-line instruction to Excel to unmerge any merged cells on the Active Sheet. This restricts the code to work on only the current sheet the user is clicked into.

ActiveSheet.Cells.Unmerge

Ending The Macro.

Once all of the merged cells are unmerged, the code ends.

End Sub

Test Your Code. Unmerge Cells.

Now all you need to do is test your Excel Macro.  This is my favorite part!. So, that is how to quickly unmerge cells in Excel.

 

Excel Macro

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts