Link to home
Start Free TrialLog in
Avatar of Wangstaa
Wangstaa

asked on

Merged cell range

Hi,

I would like a formula or custom vba formula to spit out the beginning and the end of a merged cell row number, any help would be appreciated.

for example, I have A1:A4 merged, and I would like =mergebeg(A1)=1 and =mergeend(A1)=4
ASKER CERTIFIED SOLUTION
Avatar of [ fanpages ]
[ fanpages ]

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can combine fanpages' code into one UDF like this.

Function MergedRange(objCell As Range, Optional strEnd As String = "")
If UCase(strEnd) = "END" Then
    MergedRange = objCell.MergeArea.Row + objCell.MergeArea.Rows.Count - 1
Else
    MergedRange = objCell.MergeArea.Row
End If
End Function

Open in new window

To get beginning: =MergedRange(A1)
To get end: =MergedRange(A1,"end")
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of [ fanpages ]
[ fanpages ]

Marty:
"Actually fanpages' and my modifications above only work for vertically oriented merged ranges"

My code works with a single cell (one column/one row), a merged range on one row (i.e. multiple columns), a merged range in one column (i.e. multiple rows), or a merged range on multiple rows & multiple columns.

Also, there was no need to combine the two functions I provided.

The requirement of the question was to provide two functions!
My code works with a single cell (one column/one row), a merged range on one row (i.e. multiple columns), a merged range in one column (i.e. multiple rows), or a merged range on multiple rows & multiple columns.
Your code gives only the rows. That may be what the user wants, but it's not clear to me that it is.

Also, there was no need to merge the two functions I provided.

The requirement of the question was to provide two functions!
Need? No. I was just providing a possible alternative.
Thanks for closing the question, Wangstaa.

Marty:
The question was clear to me...

"Hi,

I would like a formula or custom vba formula to spit out the beginning and the end of a merged cell row number, any help would be appreciated.

for example, I have A1:A4 merged, and I would like =mergebeg(A1)=1 and =mergeend(A1)=4"

You snagged a split of the points in any respect.

Well done.
@Wangstaa: fanpages seems to be upset with me so if you like, please 'Request Attention' and ask a moderator to reopen the question so that you can give all the points to him.
Please leave the question closed & do not re-allocate the points.

It is not the first time I have seen this occur, but as long as Martin Liss can see the issue, hopefully he will be mindful of repeating this behavio(u)r in other threads.
Avatar of Wangstaa

ASKER

Sorry guys, i didn't realize points were so important...
"Sorry guys, i didn't realize points were so important..."

They are not.

Trampling over a thread with suggestions of little value (& an admission of not reading the question) is an issue, for me, though.

Given Martin's longevity at the site, he should know better.
Thanks thermoduric.

Sorry you were asked to be involved, but I appreciate your input.