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?
 
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
 
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
You can continue it in the thread please, that way you will receive more help, from the people who already tried to help.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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

VBA Code:
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
 
Upvote 0
The following selects the range of cells in column O of the rows of the last merge range.
I hope this helps you.

VBA Code:
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
 
Upvote 0
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.
screenshot.PNG
 
Upvote 0
is this possible with Macro? or any other suggestion would also help
 
Upvote 0
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"?
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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