loop through merged cells in a Column to get their addresses

[Sagar0650]

I have one column. say column A, which has multiple merged cells of different ranges.
for example first cell is merged from A2 to A15 whereas second merged cell ranges from A16 to A115
now i want to run through entire column to get the address of each merged cell.
i have code which helps me to get the address of the only first merge cell.
can anyone help me to run a loop to get these cell address?

 

[Peter_SSs]

Try this

Sub MergedAddresses()
  Dim sAddresses As String, sFirstAddr As String
  Dim rFound As Range

  With Application.FindFormat
    .Clear
    .MergeCells = True
    With Columns("A")
      Set rFound = .Find(What:="", SearchFormat:=True)
      If Not rFound Is Nothing Then
        sFirstAddr = rFound.Address
        Do
          sAddresses = sAddresses & ", " & rFound.MergeArea.Address(0,0)
          Set rFound = .Find(What:="", After:=rFound, SearchFormat:=True)
        Loop Until rFound.Address = sFirstAddr
      End If
    End With
    .Clear
  End With
  MsgBox Mid(sAddresses, 3)
End Sub

 

[DanteAmor]

Try this

Sub get_merged_cells()
  Dim c As Range, dic As Object
  Set dic = CreateObject("scripting.dictionary")
  For Each c In Range("A1", Range("A" & ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row))
    If c.MergeCells Then dic(c.MergeArea.Address(0, 0)) = Empty
  Next
  MsgBox Join(dic.keys, ", ")
End Sub

 

[Yongle]

another option

Sub MergedCells()
    Dim cel As Range, rng As Range
    For Each cel In ActiveSheet.UsedRange.Resize(, 1)
        If cel.MergeCells Then
            If rng Is Nothing Then Set rng = cel Else Set rng = Union(rng, cel)
        End If
    Next
    If Not rng Is Nothing Then MsgBox Replace(rng.Address(0, 0), ",", vbCr), , "Merged Cells"
End Sub

 

[Peter_SSs]

another option

This suggestion combines the address of multiple merged areas if they are adjacent - like the example in post 1. Could be wrong, but I don't think the OP wants that.

 

[Yongle]

This suggestion combines the address of multiple merged areas if they are adjacent - like the example in post 1. Could be wrong, but I don't think the OP wants that.

Another version, which keeps each merged area separate ...

Sub Merged_Cells()
    Dim Cel As Range, Msg As String, Addr As String
    For Each Cel In ActiveSheet.UsedRange.Resize(, 1)
        If Cel.MergeCells Then
            Addr = Cel.MergeArea.Address(0, 0)
            If InStr(Msg, Addr) = 0 Then Msg = Msg & vbCr & Addr
        End If
    Next
    If Not Msg = "" Then MsgBox Msg, , "Merged Cells"
End Sub

 

[Sagar0650]

Try this

Thank you peter.
i can see that the result is combined addresses of all the merged cells present in column A.
is it possible to get the index numbers only for each merged cell, for example if cell is merged from A2 to A10 then i should get 2 & 10 as the result.
again thank you so much your efforts.

 

[DanteAmor]

Hi @Sagar0650, how about:

Sub get_merged_cells()
  Dim c As Range, dic As Object
  Set dic = CreateObject("scripting.dictionary")
  For Each c In Range("A1", Range("A" & ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row))
    If c.MergeCells Then dic(c.MergeArea.Cells(1).Row & " & " & c.MergeArea.Rows.Count + c.MergeArea.Cells(1).Row - 1) = Empty
  Next
  MsgBox Join(dic.keys, vbLf)
End Sub

 

[Sagar0650]

Hi @Sagar0650, how about:

Hello DanteAmor,
is it possible to get those numbers in different variables?
like for A2 to A8 it should get 2 in one variable & 8 into another one.
sorry for not able to clear myself before & thank you for your efforts.

 

[DanteAmor]

Hello DanteAmor,
is it possible to get those numbers in different variables?
like for A2 to A8 it should get 2 in one variable & 8 into another one.
sorry for not able to clear myself before & thank you for your efforts.

But if you have several ranges?
In how many variables do you want the results?
In fact, in my macro the ranges are housed in an arrangement.
You can explain why you need the numbers of the ranges, that is, what the ultimate goal is, because maybe we are only guessing what you need.