loop through merged cells in a Column to get their addresses

Sagar0650

Board Regular
Joined
Nov 25, 2019
Messages
55
Office Version
  1. 2019
Platform
  1. 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?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,722
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top