|Select all of your Merged Cells and create a Named Range:|
With all of the Merged Cells selected, click in the Name Box above Column A and type in a Name, e.g. myMergedCells. Press Enter before clicking out of the box to save the Name.
Test it by clicking in any single cell and then clicking the drop down arrow in the Name Box. You should see the name that you created and if you choose it, all of your merged cells will be selected.
Once you have created the Named Range, this code should clear all of the cells in the range, assuming you used myMergedCells.
'Loop through Named Range, clearing contents
For Each m_cell In Range("myMergedCells")
BTW...merged cells can some times be a pain, especially if you want format a range of cells that includes some of those merged cells.
Essentially the same formatting can be accomplished by selecting the cells that you want to "merge" and format them as Center Across Selection instead. This will center your text as if the cells are merged, but each cell will retain it's own identity.
If you use this method and create a Named Range from those single cells, the code would not have to loop through each cell and you could clear the entire Range with one instruction:
You could also simply choose that Named Range from the Name Box and press Delete, something you can't do with a Named Range full of merged cells.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.