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

icordeiro

New Member
Joined
Dec 13, 2016
Messages
25
Code:
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:

Code:
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.
 
Last edited by a moderator:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
What is wrong with..

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

or

Code:
Dim mRange2 As Excel.Range
 Set mRange2 = ActiveSheet.Range("DocID")
 mRange2.Cells(1,1).ClearContents
 
Last edited:
Upvote 0
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
 
Upvote 0
It doesn't for me, what does
Code:
Range("D26").ClearContents
do?
 
Last edited:
Upvote 0
Try using:

Code:
mRange2.Value2 = vbNullString
 
Upvote 0
I fixed it with

Code:
Dim mRange As Excel.Range
 Set mRange = src.Range("Desc").MergeArea
 mRange.UnMerge
 
 src.Range("D26").ClearContents
 
 src.Range("D26:G26").Merge
 
Upvote 0
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.
 
Last edited:
Upvote 0
MARK858 thanks but only for aesthetic reasons I'm not using center across selection. I want the content to be aligned to the left.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top