I'm not sure how to get around merged cells accounting for each cell within the range that is merged, but I'm trying to prevent the user from selecting a combination of cells next to one another and clearing contents.
Sub PreventDeleteMultipleMergedCells()
If Selection.Count = 1 Then
Selection.ClearContents
Else
MsgBox "You cannot delete the contents of multiple selected cells."
End If
End Sub
I came across a public function (see below) in my research, but I'm not sure how to work the output into the sub function that would trigger the MsgBox if the selected cells > 1.
Public Function MergedColumnsCount(c As Range) As Long
MergedColumnCount = 0
For i = 1 To c.Columns.Count
MergeColumnSize = c(i).MergeArea.Columns.Count
If MergeColumnSize > 1 Then
i = i + MergeColumnSize - 1
End If
MergedColumnCount = MergedColumnCount + 1
Next i
MergedColumnsCount = MergedColumnCount
End Function
rng.MergeCells
- Determines if the range or style contains merged cells.
Sub CheckSingleCell()
Dim selectedRange As Range
Set selectedRange = Selection
If IsSingleCell(selectedRange) Then
MsgBox "The selected range consists of only one cell."
Else
MsgBox "The selected range consists of multiple cells."
End If
End Sub
Function IsSingleCell(rng As Range) As Boolean
' Check if the selection is a single cell
If rng.Cells.Count = 1 Then
IsSingleCell = True
' Check if the selection is a single merged cell
ElseIf rng.MergeCells Then
IsSingleCell = True
Else
IsSingleCell = False
End If
End Function