Clear Contents of Merged Cells

[Lindsay0385]

Hi - Just a general question, as I'm confused about this. A while ago I created a macro with the macro recorder that uses relative references. I was able to clear the contents of merged cells while using that macro and didn't think anything of it, as it worked as expected.

Example (where A1:K1 is a merged cell, F3:K3 are merged, etc.)

ActiveCell.Offset(1, 2).Range("A1:K1,F3:K3,H5:K5,F7:K8").ClearContents

But I tried to reuse the code in another project by copying and pasting the code and changing the values, every time I ran the macro, I got an error message saying that it wasn't possible do to that with a merged cell.

Eventually I remembered that I created the original macro via recording, and I did the same for my new project and it worked perfectly. The code looks exactly the same as what I typed, but it works without errors.

Why would this happen? So strange.

Thanks,

Lindsay

 

[Joe4]

Eventually I remembered that I created the original macro via recording, and I did the same for my new project and it worked perfectly. The code looks exactly the same as what I typed, but it works without errors.

Are you sure it is EXACTLY the same?
I ask, because I don't think that anything you record will have the Offset function in it.

 

[jim may]

you could add line:

ActiveCell.Offset(1, 2).Range("A1:K1,F3:K3,H5:K5,F7:K8").MergeCells = False

to remove the effect of the merged cells. Is that what you want?

 

[Fluff]

Assuming A1 is the active cell when you run that code, it is actually trying to clear the contents of C2:M2 rather than A1:K1

 

[Lindsay0385]

Are you sure it is EXACTLY the same?
I ask, because I don't think that anything you record will have the Offset function in it.

The recorded macro (with relative references button selected) used the Find function to find a specific cell on a form and based on that cell copy and paste specific rows and then clear contents of the merged cells, which are a form entry, in case there were values in the form prior to copying. It was exactly the same formula, as I copied in pasted it from the version that worked, but just edited the offset values to work on my new sheet.

Here's a part of my recorded macro:

    Cells.Find(What:="copy here", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    
    ActiveCell.Rows("1:12").EntireRow.Select
    Selection.Copy
    ActiveCell.Offset(12, 0).Rows("1:1").EntireRow.Select
    Selection.Insert Shift:=xlDown
    
    ActiveCell.Offset(1, 2).Range("A1:K1,F3:K3,H5:K5,F7:K8").ClearContents

It works great, I'm just questioning why a recorded macro is able to Clear Contents of merged cells, but when I copy and paste the code to another worksheet, it doesn't work and gives me error messages.