locked
Excel VBA ClearContents of name range having merged cells RRS feed

  • 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

    Thursday, June 2, 2016 9:01 PM

Answers

  • 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

    • Marked as answer by zaveri cc Thursday, June 9, 2016 2:36 PM
    Thursday, June 2, 2016 10:09 PM
  • 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.

    Friday, June 3, 2016 5:04 AM

All replies

  • 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

    • Marked as answer by zaveri cc Thursday, June 9, 2016 2:36 PM
    Thursday, June 2, 2016 10:09 PM
  • 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.

    Friday, June 3, 2016 5:04 AM