How to check for Horizontally merged cells

  • Thread starter Thread starter APP OEU
  • Start date Start date
A

APP OEU

In my spreadsheet, users are allowed to merge cells in a single
column, but not across rows.

How can I check (in VBA) that the selection contains either a single
cell, or a merged block in a column, and allow the process to
continue, but if the selection contains cells merged across rows, then
present an error message and stop.

Many thanks.

Rick
 
This worked ok for me in xl2003:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim ErrorInMergedCells As Boolean

Set myRng = Selection

ErrorInMergedCells = False
If myRng.Cells.Count = 1 Then
'everything's ok?
ElseIf myRng.MergeCells = False Then
'no merged cells in selection, don't test anything
'true means that the selection consists of merged cells
'null means that the selection has a mixture of
'merged cells and non-merged cells
Else
For Each myCell In myRng.Cells
If myCell.MergeCells Then
If myCell.MergeArea.Columns.Count > 1 Then
ErrorInMergedCells = True
MsgBox "You have merged cells across columns " _
& "in the selection." & vbLf & _
"Like: " & myCell.Address & _
" merged across columns " & myCell.MergeArea.Address
Exit For
End If
End If
Next myCell
End If

If ErrorInMergedCells Then
'do nothing
Else
'do the real work??
End If

End Sub
 
Back
Top