Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of Merged Cells
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of Merged Cells
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of Merged Cells
Option Explicit
Sub testme() MsgBox ActiveSheet.Range("a1").Address MsgBox ActiveSheet.Range("a1").MergeArea.Address End Sub Will return different values. "pgjoshi <" wrote: I have merged cells from A1 to A7. When I activate the merged range the address list shows A1. I want the the total address of the merged range i.e. A1:A7 through VB. (Activecell.address shows A1 ) Can anyone help me ? Prasad Joshi --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of Merged Cells
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 a 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 "pgjoshi " wrote in message ... I have merged cells from A1 to A7. When I activate the merged range the address list shows A1. I want the the total address of the merged range i.e. A1:A7 through VB. (Activecell.address shows A1 ) Can anyone help me ? Prasad Joshi --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Address of merged range VBA | Excel Discussion (Misc queries) | |||
How can I sort an Excel Doc containing merged & non-merged cells? | Excel Discussion (Misc queries) | |||
Autofit Merged cell Code is changing the format of my merged cells | Excel Discussion (Misc queries) | |||
how do i link merged cells to a merged cell in another worksheet. | Excel Worksheet Functions | |||
Sorting merged cellsHow do I sort merged cells not identically siz | Excel Worksheet Functions |