Working with merged cells

[cycle_simon]

I have been trying to develop a macro to clear all types of cell contents from only unlocked cells on the sheet. The sheets also contain locked and unlocked merged celll areas which looping through the cells does not handle. I haven't been able to find the right property to identify if the cell is part of a merged range and if so, to clear the contents from only unlocked merged cells. I have tried the cell.mergeCells property and some other permutations but can't seem to find the right approach.

Appreciate any suggestions. (As a VBA newbie, I would also appreciate any comments on whether the loops used are an efficient way to handle the task.):dunno


Set rangLast = Range("A1").SpecialCells(xlCellTypeLastCell)

llastcolumn = rangLast.Column
llastrow = rangLast.Row

r = 1
Do Until r = llastrow + 1
c = 1
Do Until c = llastcolumn + 1

If Cells(r, c).Locked = False Then

Cells(r, c).Activate
Cells(r, c).ClearContents 'clear content of cell
Cells(r, c).ClearComments 'clear any cell comments
Cells(r, c).Interior.ColorIndex = 0 'set background colour to no fill

Else

If cell.MergeCells Then

Cells(r, c).Activate
Cells(r, c).ClearContents 'clear content of any unprotected cells
Cells(r, c).ClearComments 'clear any cell comments
Cells(r, c).Interior.ColorIndex = 0 'set background colour to no fill
End If
End If

c = c + 1
Loop

r = r + 1
Loop

Cells.Range("a1").Activate 'make the active cell the top left

End If

[Bob Phillips]

Motto: never used merged cells, they are more trouble than they are worth.

[cycle_simon]

What are the alternatives?

[Bob Phillips]

What does a merged cell give you that is so wonderful? The alternative is n o merged cells. I manage to live without them as do most other developers that I know.

[Paul_Hossler]

Comments (since you asked) -- this is only my style / habit / preferances / opinion

I'm not clear on what you want to actually accomplish, but not important to answering your question

1. I use Option Explicit
2. I like to specificly specify a worksheet by name (almost always)
3. For Next looping can be useful and flexible
4. .MergeArea will make working with merged cells easier when you want to use them
5. With ..... will avoid a lot of typing, and make code more readable

Paul



Option Explicit
Sub Test()
Dim rCell As Range, rData As Range
With Worksheets("Sheet1")
Set rData = Range(.Range("A1"), .Cells.SpecialCells(xlCellTypeLastCell))
End With

For Each rCell In rData
With rCell
If .MergeCells Then
.MergeArea.ClearContents 'clear content of cell
.MergeArea.ClearComments 'clear any cell comments
.MergeArea.Interior.ColorIndex = 0 'set background colour to no fill

ElseIf Not .Locked Then
.ClearContents 'clear content of any unprotected cells
.ClearComments 'clear any cell comments
.Interior.ColorIndex = 0 'set background colour to no fill
End If
End With
Next

Worksheets("Sheet1").Range("a1").Select 'make the active cell the top left

End Sub

[TrippyTom]

I have to agree with XLD. Merged cells are the devil!

Alternative:
1) select the cells you want to "merge"
2) press Ctrl + 1
3) on the Alignment tab, under Text alignment > Horizontal .. choose "Center Across Selection"

IMO, MS should never have given us the option to merge cells. They are evil evil evil!