vba excel, get start range and end range of a vertically merged cell

Question

Hi.. actually my question is just like the title says.. i need to find out the first cell and the last cell of a vertically merged cell..

lets say i merge the Cells B2 down to B50 how can i get in VBA the start cell(=B2) and the end cell(=B50) ?

thanks a lot

Answer 1

Well, assuming you know the address of one of the cells in the merged range, you could just select the offset from that range and get the row/column:

Sub GetMergedRows()
    Range("A7").Select 'this assumes you know at least one cell in a merged range.
    ActiveCell.Offset(-1, 0).Select
    iStartRow = ActiveCell.Row + 1
    Range("A7").Select
    ActiveCell.Offset(1, 0).Select
    iEndRow = ActiveCell.Row - 1
    MsgBox iStartRow & ":" & iEndRow
End Sub

The code above will throw errors if the offset row cannot be selected (i.e. if the merged rows are A1 through whatever) so you will want to add error handling that tells the code if it can't offset up, the top rows must be 1 and if it can't go down, the bottom row must be 65,536. This code is also just one dimensional so you might want to add the x-axis as well.

Answer 2

If you want the cell references as strings, you can use something like this, where Location, StartCell, and EndCell are string variables.

Location = Selection.Address(False, False)
Colon = InStr(Location, ":")
If Colon <> 0 Then
    StartCell = Left(Location, Colon - 1)
    EndCell = Mid(Location, Colon + 1)
End If

If you want to set them as ranges, you could add this, where StartRange and EndRange are Range objects.

set StartRange = Range(StartCell)
set EndRange = Range (EndCell)

Answer 3

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