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

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

nms(i).RefersToRange.MergeArea.ClearContents

"Richard" wrote:

> 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
>
>

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

nms(i).RefersToRange = ""

may work.

Richard wrote:
>
> 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

--

Dave Peterson

That did it - THANKS !!

Richard