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?

 

[Sagar0650]

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.

I have messaged you in separate conversation.
please check & let me know your feedback.
Thank you for support

 

[DanteAmor]

You can continue it in the thread please, that way you will receive more help, from the people who already tried to help.

 

[Sagar0650]

Hello DanteAmor

Sub Macro()

Dim rng As Range
Dim rngStart As Range
Dim rngEnd As Range
Dim s As Integer, e As Integer

Set rng = Range("A110")

If rng.MergeCells Then

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

'getting the row index numbers in variables
s = rngStart.Row
e = rngEnd.Row

'go to the last present column & select the range a per the index numbers
Range("O" & s, "O" & e - 1).Select

Else
MsgBox "Not merged area"
End If
End Sub

There is no specific reason behind selecting column O.
i just want to select last column present in the sheet to select the required range.

 

[DanteAmor]

Following your example, you have 2 merge ranges: A2 to A15 and A16 to A115.
Which of the 2 do you want to select, or do you just want to select one range, but which one do you want to select the first or the second?
And would you have more ranges? you can explain.

 

[Sagar0650]

consider A16 to A115.
now i have the index numbers so i want to go to the last column of the sheet & select the range of cells as per the index numbers.

 

[Peter_SSs]

I think we still need more information about just what you are trying to achieve. Selecting ranges in code is rarely required to do whatever comes next and it slows your code considerably.

Also, when posting code, please indent your code and use code tags to preserve that indentation. My signature block below has help on that or look here

To get the first & last rows of each merged area into variables, try this. At the end of the code, the first and last rows of the 1st merged range will be stored in
MA(1)(0) and MA(1)(1)

The 2nd merged range rows will be strored in
MA(2)(0) and MA(2)(1)

etc

Sub MergedAddresses_v2()
  Dim sFirstAddr As String
  Dim rFound As Range
  Dim MA As Variant
  Dim k As Long
 
  With Application.FindFormat
    .Clear
    .MergeCells = True
    With Intersect(ActiveSheet.UsedRange, Columns("A"))
      ReDim MA(1 To .Rows.Count)
      Set rFound = .Find(What:="", SearchFormat:=True)
      If Not rFound Is Nothing Then
        sFirstAddr = rFound.Address
        Do
          k = k + 1
          MA(k) = Split(Replace(rFound.MergeArea.Address(0, 0), "A", ""), ":")
          Set rFound = .Find(What:="", After:=rFound, SearchFormat:=True)
        Loop Until rFound.Address = sFirstAddr
        ReDim Preserve MA(1 To k)
      End If
    End With
    .Clear
  End With
End Sub

 

[DanteAmor]

The following selects the range of cells in column O of the rows of the last merge range.
I hope this helps you.

Sub get_merged_cells_2()
  Dim c As Range, d As Range
  For Each c In Range("A1", Range("A" & ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row))
    If c.MergeCells Then Set d = c.MergeArea
  Next
  If Not d Is Nothing Then d.Offset(, 14).Resize(d.Rows.Count).Select
End Sub

 

[Sagar0650]

sincere apology for not able to clear my requirement at first.
my actual requirement is:

• Determine the row-index numbers for first merge cell then I should go to last column to select the same range of cells.
• Next step is to find the largest number present from that range.
• Last step is to find the corresponding value from “col5” of that large number. Use of msgbox to display the result.

This should continue through the column A till the last merge column
please find the attach image for more details.

 

[Sagar0650]

is this possible with Macro? or any other suggestion would also help

 

[DanteAmor]

Still it doesn't seem like a real requirement.
The last column is always column I?
Column 5 is always column E?
In your example, you should write the expected result, that is, for the first rank what the data is, it is perhaps "output2".
So, according to your example you want in a msgbox:
"output2, output13 and output24"?