ClearContents of merged cells?

[TraceyH]

Hi. I wrote this but am getting an error that it can't do it as some of the cells are merged:


Sub Event_AddNew()
Form.Range("D3,E5:E11,E15:E22,H9:H13,J13,K5:K13,M13,N13,N22,P13,R2").ClearContents
On Error Resume Next
Form.Shapes("EventPic").Delete
On Error GoTo 0
End Sub

I've then tried this, but get an "application-defined or object-defined error":


Sub Event_AddNew()
Form.Range("D3,E5:E11,E15:E22,H9:H13,J13,K5:K13,M13,N13,N22,P13,R2").MergeArea.ClearContents
On Error Resume Next
Form.Shapes("EventPic").Delete
On Error GoTo 0
End Sub

Does anyone have any better suggestions, please?

[jolivanes]

What is Form?

[Paul_Hossler]

I added CODE tags to your post - use the [#] icon to insert the tags and paste your macro between them


Merged cells can be tricky. Many people here strongly suggest to never use them

Personally, I think that if you're careful, it's ok to use then

Only thing I've found is that you need to make sure that all the cells that are merged are included

So if you have D3, D4, and D5 merger, try


Range("D3:D5,E5:E11,E15:E22,H9:H13,J13,K5:K13,M13,N13,N22,P13,R2").ClearContents




There are two properties that are helpful

In my little test, A1:E1 are merged

28515




Option Explicit

Sub test()


With ActiveSheet.Range("A1")
MsgBox .MergeCells ' boolean
MsgBox .MergeArea.Address ' range


End With


'works
Range("A1:E1,B2,C3,D4:E10").ClearContents

'works
Range("A1:E2,C3,D4:E10").ClearContents

'doesn't work
Range("A1,B2,C3,D4:E10").ClearContents

'doesn't work
Range("A1:C1,B2,C3,D4:E10").ClearContents
End Sub

[TraceyH]

You're a star, Paul, thanks - it's long and nasty looking but it works! I would love not to have merged cells, but am trying to make the form as visually appealing as possible!

Sub Event_AddNew()
Form.Range("D3,E5:H5,E7:H7,E9:E11,E15:N17,E19:N20,E22:F22,H9:H13,J13,K5:N5,K7:N7,K9:N9, K13:N13,N22,P13:Q22,R2").ClearContents
On Error Resume Next
Form.Shapes("EventPic").Delete
On Error GoTo 0
End Sub

[Paul_Hossler]

The other way is to just do it piece-meal



With Form
.Range("D3").ClearContents
.Range("E5:H5").ClearContents


etc

[TraceyH]

Good to know for future reference, thank you!