Clear merged cells with VBA, any difference between the two formulas?

Hi,

I want to automatically clear cells across my workbook with VBA (to empty it). Certain cells are merged so the common approach of .clearcontents does't really work properly.

It seems that the following two approaches work correctly and I'm wondering if one should be used over the other:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

mRange2 is my named range.

Thanks

Please Login or Register  to view this content.

Change Range reference as required

Hi,

try mRange2.MergeArea(1).Value2 = Empty …

Thanks to both. It seems like the two approaches I mention in my initial message both work so the questions really is: is there a reason I should use one over the others (incl. the ones you suggest)?

Thanks

jolivanes made some mistakes in his code. We check whether the cells are merged with the MergeCells property. When we run the loop through all cells of the range, the line

Please Login or Register  to view this content.

will work correctly only for the first cell of the merged range, for the rest it will report an error. And there is no need to test whether the cells are merged. You can use MergeArea for any cell. More correct code (with merged cells check)

Please Login or Register  to view this content.

or without checking

Please Login or Register  to view this content.

In contrast, the code suggested by Marc L could look like this:

Please Login or Register  to view this content.

Sometimes I see codes where users clean cells with an empty string (""). In my opinion, this is not the correct solution. The Clear, ClearContents methods should be used, but these should be used in conjunction with MergeArea. And if we don't want to use these methods, cells can be cleared by assigning them Null. There are subtle differences between the "", vbNullString, Empty, Null assignments, but these are "invisible" to the average user. Personally, I recommend the use of Null. For example

Please Login or Register  to view this content.

or shorter the same

Please Login or Register  to view this content.

Artik

As when reading some blank range from a brand new worksheet in an array the content is Empty and not Null …

Thanks everyone, appreciate the feedback. Just to be clear, what is the difference between the code I'm mentioning in my initial post (which seems to work) and the more complex ones proposed in the answers? Is there a particular reason to use the more complex ones?

Thanks