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
Table of contents
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.