PDA

View Full Version : finding merged cells



figment
08-31-2007, 10:24 AM
I realize that there is a greatdeal of dislike for merged Cells, but i have a project were i can't avoid them and need to know how to find out if a cell is hiden behind a merged block?

example:

cells A1:C1 are merged, how do i know if i am looking a the displayed cell in the merged block aka A1, or the two hidden B1 and C1

mvidas
08-31-2007, 10:40 AM
Hi figment,

A range object has a .MergeArea property, which returns either a) a range object of the merged area if it is part of a merged cell, or b) the original range if not.

Using your example of A1:C1, and then looking at cells A1, C1, and E1, you can see what I mean using:Sub FigmentCheckMerged()
Range("A1:C1").MergeCells = True
CheckMerged Range("A1")
CheckMerged Range("C1")
CheckMerged Range("E1")
End Sub
Sub CheckMerged(RG As Range)
If RG.MergeArea.Address = RG.Address Then
MsgBox "Cell " & RG.Address(0, 0) & " is not in a merged area"
Else
MsgBox "Cell " & RG.Address(0, 0) & " is in merge area " & RG.MergeArea.Address(0, 0) _
& " ('displayed cell' is " & RG.MergeArea.Cells(1, 1).Address(0, 0) & ")"
End If
End SubI edited my original code to show it as a funciton

Bob Phillips
08-31-2007, 11:01 AM
You could just check the Mergecells property.

figment
08-31-2007, 11:50 AM
thank you vary much for the help. i had not realize that the cells call with in mergedarea, had a diffent 0,0 point then the rest of the sheet

thanks for the help

unmarkedhelicopter
08-31-2007, 01:14 PM
No, (0, 0) is just a shorthand way of saying (False, False), which means you DON'T want an absolute address for columns and you DON'T want an absolute adress for rows.

figment
08-31-2007, 05:32 PM
sorry i am a cad operator, so by (0,0) i meant starting point. i am actually referring to the cell(1,1) of this statement

RG.MergeArea.Cells(1, 1).Address(0, 0)

once again thanks for the help

mdmackillop
09-01-2007, 02:02 AM
FYI This is applicable to any specified range
Sub StartPoint()
Dim rng As Range
Set rng = Range(Cells(10, 10), Cells(50, 50))
MsgBox rng(1, 1).Address
End Sub

Bob Phillips
09-01-2007, 02:26 AM
sorry i am a cad operator, so by (0,0) i meant starting point. i am actually referring to the cell(1,1) of this statement

RG.MergeArea.Cells(1, 1).Address(0, 0)

once again thanks for the help

The 1,1 is the starting point. The 0,0 is telling address to be output as relative, B10 not $B$10 say.


It would be better, much better IMO, to use

RG.MergeArea.Cells(1, 1).Address(False, False)

Bob Phillips
09-01-2007, 02:26 AM
PS but as I said earlier, MergeCells property is even better