Merged Cell Offset Inconsistencies

[pbornemeier]

I ran across a problem when I had to deal with merged cell offsets and thought I would share my results,

Summary: Avoid offsets from merged cells unless you test all results. Some offset directions return the address that would be expected if the cell was not part of a range, others return a cell off the edge of the merged area.

I am not sure if this should be counted as a bug, but it is an inconsistency.

Option Explicit

Sub MergedCellsOffsetProblemDemo()
    'Creates a merged range of 3x3 cells and displays the 1 cell offset ranges from each cell
    
    Const sULCell As String = "B2"
    Dim lDeltaX As Long
    Dim lDeltaY As Long
    Dim rngCell As Range
    Dim lOutputCol As Long
    Dim lOutputRow As Long
    
    Sheets.Add
    lOutputRow = 7
    With ActiveSheet
        Range(.Range(sULCell), .Range(sULCell).Offset(2, 2)).MergeCells = True
        
        For Each rngCell In .Range(sULCell).MergeArea
            lOutputRow = lOutputRow + 1
            lOutputCol = 1
            For lDeltaX = -1 To 1
                For lDeltaY = -1 To 1
                    If lOutputCol = 1 Then
                        'Write offset reference
                        .Cells(lOutputRow, lOutputCol).Value = rngCell.Address(False, False)
                    End If
                    lOutputCol = lOutputCol + 1
                    If lOutputRow = 8 Then .Cells(7, lOutputCol).Value = "'" & lDeltaX & ", " & lDeltaY
                    .Cells(lOutputRow, lOutputCol).Value = rngCell.Offset(lDeltaX, lDeltaY).Address(False, False)
                Next
            Next
        Next
        
        'Add Arrows
        Range("B6").Value = ChrW(8598)  'UL
        Range("C6").Value = ChrW(8593)  'U
        Range("D6").Value = ChrW(8599)  'UR
        Range("E6").Value = ChrW(8592)  'L
        Range("G6").Value = ChrW(8594)  'R
        Range("H6").Value = ChrW(8601)  'DL
        Range("I6").Value = ChrW(8595)  'D
        Range("J6").Value = ChrW(8600)  'DR
        Range("F6").Value = ChrW(8729)  'No Offset
        Range("B6:J6").HorizontalAlignment = xlCenter
        
        .Range("A6").Value = "Offset"
        .Range("A7").Value = "Cell"
        
        'Tint
        With Range("B8:J16").Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 10092441
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        With Range("D8:D9,D11:D12,D14:D15,G8:G9,G11:G12,G14:G15,H8:H13,I8:J15").Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 49407
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        
        .Range("F2").Value = "Merged cells offset inconsistency demo."
        .Range("F3").Value = "Merge B2:D4 and determine result of 1-cell offset."
        .Range("B19").Value = "Green cells mark where the offset is 1 cell away in the expected direction."
        .Range("B20").Value = "Orange cells mark where the offset is NOT 1 cell away in the expected direction."
        
    End With

End Sub

 

[Joe4]

Merged cells are a known problem in Excel and wreak havoc with all sorts of things, expecially VBA and sorting.
As such, it is generally advised to avoid using merged cells whenever possible (I believe that they are probably the single, worst thing in Excel).

Note that for instances in which you are merging across multiple columns within a single row, you can use the Center Across Selection formatting option.
See here: https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/

It looks like you are merging down rows too, so that probably doesn't help with your particular issue. But just wanted to mention for others you might come across this thread.