cycle_simon
01-24-2008, 08:41 AM
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
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