loop through merged cells in a Column to get their addresses

Sagar0650

New Member
Joined
Nov 25, 2019
Messages
28
Office Version
2019
Platform
Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,790
Office Version
365
Platform
Windows
Try this
VBA Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
Try this

VBA Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,261
another option

VBA Code:
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
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,790
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,261
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 ...:)

VBA Code:
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

New Member
Joined
Nov 25, 2019
Messages
28
Office Version
2019
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
Hi @Sagar0650, how about:

VBA Code:
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
1574971545811.png
 

Sagar0650

New Member
Joined
Nov 25, 2019
Messages
28
Office Version
2019
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
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.
 

Forum statistics

Threads
1,078,253
Messages
5,339,111
Members
399,279
Latest member
danidanidaniel

Some videos you may like

This Week's Hot Topics

Top