Get start range and end range of a vertically merged cell with Excel using VBA

Question

I need to find out the first cell and the last cell of a vertically merged cell..

Let's say I merge Cells B2 down to B50.
How can I get in VBA the start cell(=B2) and the end cell(=B50)?

Answer 1

Sub MergedAreaStartAndEnd()

    Dim rng As Range
    Dim rngStart As Range
    Dim rngEnd As Range

    Set rng = Range("B2")

    If rng.MergeCells Then

        Set rng = rng.MergeArea
        Set rngStart = rng.Cells(1, 1)
        Set rngEnd = rng.Cells(rng.Rows.Count, rng.Columns.Count)

        MsgBox "First Cell " & rngStart.Address & vbNewLine & "Last Cell " & rngEnd.Address

    Else

        MsgBox "Not merged area"

    End If

End Sub

Answer 2

Below macro goes through all sheets in a workbook and finds merged cells, unmerge them and put original value to all merged cells.

This is frequently needed for DB applications, so I wanted to share with you.

Sub BirlesenHucreleriAyirDegerleriGeriYaz()
    Dim Hucre As Range
    Dim Aralik
    Dim icerik
    Dim mySheet As Worksheet

    For Each mySheet In Worksheets

    mySheet.Activate
    MsgBox mySheet.Name & “ yapılacak…”

    For Each Hucre In mySheet.UsedRange
        If Hucre.MergeCells Then
           Hucre.Orientation = xlHorizontal
           Aralik = Hucre.MergeArea.Address
           icerik = Hucre
           Hucre.MergeCells = False
           Range(Aralik) = icerik
        End If
    Next

 MsgBox mySheet.Name & " Bitti!!"

 Next mySheet
End Sub