Address of Merged Cells

[pgjoshi]

I have merged cells from A1 to A7. When I activate the merged range th
address list shows A1. I want the the total address of the merged rang
i.e. A1:A7 through VB. (Activecell.address shows A1 ) Can anyone hel
me ?

Prasad Josh

 

[mangesh_yadav]

I don't think there's a direct way, but you could check the address o
the next cell and then reduce by one to get the range.

- Manges

 

[Dave Peterson]

Option Explicit
Sub testme()

MsgBox ActiveSheet.Range("a1").Address
MsgBox ActiveSheet.Range("a1").MergeArea.Address

End Sub

Will return different values.

 

[TroyW]

Prasad,

Here are a couple of examples. Test1 evaluates the current selection on the
worksheet. Test2 can be used to find if a given cell address is part of a
merged cell range. I used the RowAbsolute and ColumnAbsolute parameters to
remove the "$" from the cell address (i.e. $A$1:$A$7 ==> A1:A7).

Troy


Sub Test1()
Dim rng1 As Range

If TypeName(Selection) = "Range" Then
Set rng1 = Selection
If Selection.MergeCells = True Then
Debug.Print "Merged = " & rng1.MergeCells
Debug.Print "Cell Count = " & rng1.Count
Debug.Print "Row Count = " & rng1.Rows.Count
Debug.Print "Column Count = " & rng1.Columns.Count
Debug.Print "Address = " & rng1.Address( _
RowAbsolute:=False, _
ColumnAbsolute:=False)
Else
Debug.Print "Selection not merged"
End If
End If
End Sub


Sub Test2()
Dim rng1 As Range
Dim rng2 As Range

'''Example: Merged cells are: A1:A7 on Sheet1.
Set rng1 = Sheet1.Range("A3")
Set rng2 = rng1.MergeArea
If rng2.MergeCells = True Then
Debug.Print rng2.Address( _
RowAbsolute:=False, _
ColumnAbsolute:=False)
Else
Debug.Print "Cell not merged"
End If
End Sub