Skip Merged cells in a worksheet

[EliseFreedman]

Hi All

I am trying to write code that will clear all previous responses in all the worksheets in a workbook. My code would work fine except for the fact that the ranges contain merged cells so the code is breaking at the merged cells. Is there any way in which I can skip the cell if it is merged?

my code is as follows

Dim wkSht As Worksheet
For Each wkSht In Worksheets
    If (Left(wkSht.Name, 1) = "S") Then
        'only run if sheet name starts with an "S"
wkSht.Range("E2:E30,F2:F30").ClearContents
      End If
'edit ranges to suit max row & cols to exclude (eg cols D,E in above
Next wkSht
End Sub

 

[mintjulep]

One more reason why you should never merge cells.

At any rate:

range(MyRange).MergeCells

will return a Boolean TRUE or FALSE if any cell in MyRange is merged.

 

[mintjulep]

Or more accurately, it will return TRUE is any cell is merged, and FALSE if all cells are not merged.

 

[SkipVought]

Eslie,

ditto to mintjulep!!!

Set ma = Range("a3").MergeArea
If ma.Address = "$A$3" Then
    MsgBox "not merged"
Else
    ma.Cells(1, 1).Value = "42"
End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bubba100]

Do you know where the merged cells are going to be?

 

[SkipVought]

bubba, you ought NEVER make assumptions like that!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Elise, also if you wish to be vicious to those horribly maligned merged cells (and rightly so), your code can unmerge them as part of the process.

If Range("A3").MergeCells Then Range("A3").MergeArea.Unmerge