Solved VBA button to Clear Content in Multiple Merged Cells.

Microsoft Microsoft office excel 2007 -...
June 17, 2013 at 11:03:14
Specs: Windows 7
To whom this may concern,

I have a "in office form" that has multiple merged cells, How can I create an excel button that can easily clear all data from all of the merged cells while leaving the cells merged so that more info can be entered.

Thank you in advance for your help and support,

See More: VBA button to Clear Content in Multiple Merged Cells.

Report •

June 17, 2013 at 12:10:28
✔ Best Answer
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.

Sub ClearMerged()
'Loop through Named Range, clearing contents
   For Each m_cell In Range("myMergedCells")
End Sub

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:

Sub ClearCentered()
End Sub

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.

Report •

June 17, 2013 at 13:54:57
You Rock Derbydad03, it worked on the first try. Thank you for the great answer!

Report •

Related Solutions

Ask Question