"Reset Form" Command Button Will not clear merged cells.

[Wulf]

I have a spreadsheet that we use quite often, and am trying to make it a little more "user-friendly". So, to facilitate rapid-resuage, I inserted a Command Button (ActiveX Control), named it "Reset Form, and have this as the code:

Private Sub CommandButton1_Click()
Range("C3").ClearContents
Range("C4").ClearContents
Range("C5").ClearContents
Range("F3").ClearContents
Range("F5").ClearContents
Range("D8").ClearContents
Range("D9").ClearContents
Range("D14").ClearContents
Range("D16").ClearContents
Range("D29").ClearContents
Range("D31").ClearContents
Range("G31").ClearContents
Range("F4") = "=VLOOKUP(F3,Sheet2!A15:C17,3,0)"
Range("D11") = "X:\Development\Database Requests to be Completed\Database Downloads"
End Sub

HOWEVER, "D" cells 14, 16, 29, and 31 are merged with the neighboring "E" cell.

When I run it, I get "Run-Time '1004': We can't do that to a merged cell".

I did several searched through the forums, and found some similar topics, and tried some of the suggested fixes, but couldn't get any to work for me.

 

[Fluff]

It's best to avoid merged cells, as they cause far to many problems.
Try using centre across selection instead.

 

[ParamRay]

Try:

Range("D14").MergeArea.ClearContents

etc.

 

[mumps]

Try:

Private Sub CommandButton1_Click()
    Range("C3").ClearContents
    Range("C4").ClearContents
    Range("C5").ClearContents
    Range("F3").ClearContents
    Range("F5").ClearContents
    Range("D8").ClearContents
    Range("D9").ClearContents
    Range("D14").MergeArea.ClearContents
    Range("D16").MergeArea.ClearContents
    Range("D29").MergeArea.ClearContents
    Range("D31").MergeArea.ClearContents
    Range("G31").ClearContents
    Range("F4") = "=VLOOKUP(F3,Sheet2!A15:C17,3,0)"
    Range("D11") = "X:\Development\Database Requests to be Completed\Database Downloads"
End Sub

You should avoid using merged cells because they almost always create havoc for Excel macros. Do a little research into "Merge Across Selection". It most often has the same effect as merging cells without the problems.

 

[Wulf]

Many thanks! the addition of the ".MergeArea" did it.

Much appreciated.

 

[mumps]

Glad we could help and thank you for the "Rep".