Solved: Clear content of a merged cell

[ThePigeon]

Hey!

What I’m trying to do is simply clear the contents of 2 different merged cells named “FirstName” and “LastName” on the click of a cmd button, which I have already created and is already carrying out some functions. But this is proving much more difficult than clearing a non-merged cell. When I try: range(“firstname”).clearcontents
This return an error saying you cannot edit a merged cell.

So I did a bit of internet research and it threw up this: http://antonio.beyah.net/blog/2008/04/08/vba-merged-cell-information/ (http://antonio.beyah.net/blog/2008/04/08/vba-merged-cell-information/)
This person appears to have worked it out, but me being very new to this I couldn’t make sense of it.
If anyone can make sense of it… and give me some help with it that would be great.
Cheers,
Joe

[ThePigeon]

forgot to say using vba in excel 2007 if it makes a difference

[lucas]

Why cause yourself the grief? Avoid merged cells at all costs. You now know why. Unmerge the cells and use center across selection.

After you unmerge them select the cells you had merged and right click-select format cells.

In the allignment tab look for horizontal alignment...drop the box and select "center across selection"

[duluter]

Assuming you can't change the fact that you have merged cells, try this:

Dim mRange As Excel.Range
Set mRange = ActiveSheet.Range("firstname").MergeArea
mRange.ClearContents
This is similar to what you found at the site you linked, but using a named range instead. I think using the MergeArea property returns a range object that represents the merged area. Seemed to work for me.


Duluter


[EDIT]

As #5 points out, it's lame to include the ActiveSheet nonsense above. My bad.

[mdmackillop]

Sub ClearAbomination()
Range("firstname").MergeArea.ClearContents
End Sub

[lucas]

A little strong Malcolm, but you know how to make a point.....:yes

[ThePigeon]

solved!!!! cheers for all the help

regards,

Joe

[Stevec63]

I have this problem above currently, but I am using VBA: Retail 7.1.1071 from Office 365.
I have tried adding below to my macro but it still doesn't work.
I am not that familiar with using "Name Manager" but I believe I have used it correctly see attached pic. 20631
Any ideas


Sub Clear_Cells()
Range("G17").ClearContents
Range("G22").ClearContents
Range("G27").ClearContents
Range("B3:B21").ClearContents
Range("B26:B49").ClearContents
Range("B53:B55").ClearContents
Range("b57:b64").ClearContents
Range("E3:E12").ClearContents
Range("E17:E27").ClearContents
Range("E34:E67").ClearContents
Range("G34:G50").ClearContents
Range("G57:G64").ClearContents
Range("I34:I50").ClearContents
Range("J34:J50").ClearContents
Range("TransactionTask").MergeArea.ClearContents


MsgBox "Form has been RESET"


End Sub

[mdmackillop]

Range does not take a MergeArea. Simply

Range("TransactionTask").ClearContents

[Stevec63]

Range does not take a MergeArea. Simply

Range("TransactionTask").ClearContents

I thought I had tried this already but obviously not as it worked ..... thank you. :-)