Clear contents of merged cells vba - run-time error 1004

[icordeiro]

Dim mRange2 As Excel.Range
Set mRange2 = ActiveSheet.Range("DocID").MergeArea
mRange2.ClearContents

I have a range named 'DocID' and I would like to clear its content.

When I run the code above it does clear the content but then I get this error: Run-time error '1004': We can't do that to a merged cell

I also tried to first unmerge the cells to clear its content. I also would like to merge them again:

Dim mRange As Excel.Range
Set mRange = src.Range("Descrição").MergeArea
mRange.UnMerge
src.Range("D26").ClearContents

I get a method range objet error.

 

[MARK858]

What is wrong with..

Dim mRange2 As Excel.Range
 Set mRange2 = ActiveSheet.Range("DocID")
 mRange2.ClearContents

or

Dim mRange2 As Excel.Range
 Set mRange2 = ActiveSheet.Range("DocID")
 mRange2.Cells(1,1).ClearContents

 

[icordeiro]

DocID is a range (D26:G26) and the cells are merged.

When I run that code it gives back this error:

Run-time error '1004': We can't do that to a merged cell

 

[MARK858]

It doesn't for me, what does

Range("D26").ClearContents

do?

 

[RoryA]

Try using:

mRange2.Value2 = vbNullString

 

[icordeiro]

I fixed it with

Dim mRange As Excel.Range
 Set mRange = src.Range("Desc").MergeArea
 mRange.UnMerge
 
 src.Range("D26").ClearContents
 
 src.Range("D26:G26").Merge

 

[MARK858]

Just a suggestion but as you are only doing a horizontal merge then it might be worth you doing a Google on center across selection which will save you a lot of grief compared to merged cells.

 

[icordeiro]

MARK858 thanks but only for aesthetic reasons I'm not using center across selection. I want the content to be aligned to the left.