excelvba

Preventing ClearContents on Combination of Merged and Unmerged Cells


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

enter image description here

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

Solution

  • 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