Excel VBA ClearContents of name range having merged cells

Question

Hi All,

I have name range "RngDlt" which consist of many cells across one sheet and some of this cells are merged. I am using below code to clearcontents of my name range but i am getting error 1004- application defined or object defined error

If TypeName(Evaluate("RngDlt")) = "Range" Then Evaluate("RngDlt").ClearContents

I tried using below line but still same error persist.

If TypeName(Evaluate("RngDlt").MERGEAREA) = "Range" Then Evaluate("RngDlt").MERGEAREA.ClearContents

Thanks,

Zav

Answer 1

The TypeName and Evaluate part identifies that it refers to a range but not the address of the range. To use the name in a range reference then it needs to be coupled with Range like the following.

If TypeName(Evaluate("RngDlt")) = "Range" Then Range("RngDlt").ClearContents

---

Regards, OssieMac

Answer 2

Hi zaveri cc,

If you correct the syntax then also you will get error "we can't do that to merged cell".

to delete the contents of the mergedcell use the line of code below.

Application.Goto Reference:="RngDlt"
Selection.ClearContents

make sure Range only contain merged cells. it will not clear when there is some cells are single and some cells are merged in range.

Regards

Deepak

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.