ClearContent & 'Cannot change part of a merged cell' error

[Richard]

Hello,

I need to be able to clear the content of all the named cells that
start with "tSel_" - I've created the little function below (I'am a VBA
newbie) and I was able to develop the function below - This works
great, BUT for one thing: If the Named Cell refers to MERGED cells,
this fails with a "Cannot change part of a merged cell." error...

Any suggestions how I can fix this ?

THANKS!!
Richard

Public Function ClearCells()
Dim strFirstletter As String

Set nms = ActiveWorkbook.Names

For i = 1 To nms.Count
strFirstletter = Left(nms(i).Name, 5)
If strFirstletter = "tSel_" Then
nms(i).RefersToRange.ClearContents
End If
Next
End Function

 

[Guest]

nms(i).RefersToRange.MergeArea.ClearContents

 

[Richard]

Works great !

But it seems I get a Run-time error 1004 "Application-Defined or
object-defined error" with groups of merged cells.

For instance, if cells A1 & A2 are merged, and cells B1 & B2 are
merged, the errors appears because tSel_Group represent A1:B2 ... which
is 2 groups of merged cells.

Any workarounds for such ?

THANKS !
Richard

 

[Dave Peterson]

nms(i).RefersToRange = ""

may work.

Hello,

I need to be able to clear the content of all the named cells that
start with "tSel_" - I've created the little function below (I'am a VBA
newbie) and I was able to develop the function below - This works
great, BUT for one thing: If the Named Cell refers to MERGED cells,
this fails with a "Cannot change part of a merged cell." error...

Any suggestions how I can fix this ?

THANKS!!
Richard

Public Function ClearCells()
Dim strFirstletter As String

Set nms = ActiveWorkbook.Names

For i = 1 To nms.Count
strFirstletter = Left(nms(i).Name, 5)
If strFirstletter = "tSel_" Then
nms(i).RefersToRange.ClearContents
End If
Next
End Function

 

[Richard]

That did it - THANKS !!

Richard