More on merged cells

[Frank Rizzo]

I loop through the cells in a range and sometimes, the cell will be part
of the merged area. How do I find out whether the position of the cell
within the merged area? For instance, if the cell is 2nd from the top
and 5th from the left. How do I find out this information?

 

[Tom Ogilvy]

Dim rng as Range, cell as Range
Dim offsetrw as long, offsetcol as long
set cell = Range("B9")
set rng = Range("B9").MergeArea
if rng.Count > 1 then
offsetrw = cell.row - rng(1).row + 1
offsetcol = cell.column = rng(1).column + 1
msgbox " row offset: " & offsetrw & vbNewline & _
" column offset: " & offsetcol
End if