Cannot change part of merged cell

[rjplante]

I have several merged cells and I have the code listed below to clear all of these out. When it gets to the line highlighted in red, it crashes with the message "Cannot change part of a merged cell". The problem is that I am not trying to change part of the merged cell, I am trying to clear all of it out. Don't know what the issue is.

Thanks for the help.

Robert

'clear out the additional lots covered section.
Range("AY11:AY25").ClearContents
Range("R9:U9").ClearContents
Range("W9:AA9").ClearContents

If Range("AZ11").Value <> "" Then
    Range("R11:U11").ClearContents
    Range("W11:AA11").ClearContents
ElseIf Range("AZ13").Value <> "" Then
    Range("R13:U13").ClearContents
    Range("W13:AA13").ClearContents
ElseIf Range("AZ15").Value <> "" Then
    Range("R15:U15").ClearContents
    Range("W15:AA15").ClearContents
ElseIf Range("AZ17").Value <> "" Then
    Range("R17:U17").ClearContents
    Range("W17:AA17").ClearContents
ElseIf Range("AZ19").Value <> "" Then
    Range("R19:U19").ClearContents
    Range("W19:AA19").ClearContents
ElseIf Range("AZ21").Value <> "" Then
    Range("R21:U21").ClearContents
    Range("W21:AA21").ClearContents
ElseIf Range("AZ23").Value <> "" Then
    Range("R23:U23").ClearContents
    Range("W23:AA23").ClearContents
ElseIf Range("AZ25").Value <> "" Then
    Range("R25:U25").ClearContents
    Range("W25:AA25").ClearContents
End If

 

[VoG]

Merged cells and VBA don't get on. You need to refer to the top left cell of the merged range. Maybe

Range("W9").ClearContents

 

[rjplante]

Crazy, but now it highlights that line, with the same error message. Stupid VBA! What can I try next?

 

[VoG]

Unmerge the cells and use Center Across Selection http://www.mrexcel.com/forum/showthread.php?t=335123