VBA Cell Offset Replaces Previous Cell Value

Question

Got a problem with VBA merged cells/ offset property. I want to go through a list containing merged cells and display the range for each merged cell.

Example:

Range ("A1").Value is "Addresses"
Range ("A2:A50") is a merged cell with value "[email protected]"

When I run the code below:

Issue 1: Range ("A1").Value is transformed to "[email protected]"
Issue 2: After the offset, MergeArea.Address still returns Range ("A1") and not Range ("A2:A50")

My code:

Sub Macro1()

   Dim CellA As Range    
   Set CellA = Range("A1")

   Do Until IsEmpty(CellA.Value) = True

    If CellA.MergeCells = False Then    
     CellA = CellA.Offset(1, 0)    
    Else   
     CellA = CellA.Offset(1, 0)   
     MsgBox (CellA.MergeArea.Address)
    End If

   Loop

End

First of all using merged cells and VBA is highly discouraged, can you format your cells that you dont need merging? If not, every function regarding the merged area adresses the top left cell — senior_freshman

Dang D. Khanh Dang D. Khanh · Accepted Answer · 2020-12-16T11:54:32

try this code:

Sub Macro1()
Dim CellA As Range
Set CellA = Range("A1")
        
Do Until IsEmpty(CellA.Value) = True '
    If CellA.MergeCells Then         '
        MsgBox CellA.MergeArea.Address
        exit do
    End If
    Set CellA = CellA.Offset(1, 0)
Loop
End Sub

VBA Cell Offset Replaces Previous Cell Value

1 Answers