Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting proper range address from multiple cell selections in V
Is there a way in VBA to ascertain if a range selection is contiguous. That
is, say I select C3 and D4 on the same page. The Address property for the Selection does not automatically put the address into the form C3:D3 but instead into the form $C$3,$D$3. Is there a way to do this in VBA or do I have to write a function which tests the string for contiguous row or column headers? Sub Macro1() Dim RngAddress As String Range("C3,D3").Select RngAddress = Selection.Address Debug.Print RngAddress End Sub Immediate Window: $C$3,$D$3 Thanks EM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting proper range address from multiple cell selections in V
When Areas.Count = 1, the range is contiguous.
Dim rng as Range Set rng = Range("C3, D3") debug.print rng.areas.count debug.print rng.Address(0,0) -- Tim Zych SF, CA "ExcelMonkey" wrote in message ... Is there a way in VBA to ascertain if a range selection is contiguous. That is, say I select C3 and D4 on the same page. The Address property for the Selection does not automatically put the address into the form C3:D3 but instead into the form $C$3,$D$3. Is there a way to do this in VBA or do I have to write a function which tests the string for contiguous row or column headers? Sub Macro1() Dim RngAddress As String Range("C3,D3").Select RngAddress = Selection.Address Debug.Print RngAddress End Sub Immediate Window: $C$3,$D$3 Thanks EM |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting proper range address from multiple cell selections
The example you show should be a contiguous range but the ouput suggests the
opposite. Should this not return a "1" and not a "2"? Sub Address() Dim rng As Range Set rng = Range("C3,D3") Debug.Print rng.Areas.Count Debug.Print rng.Address(0, 0) End Sub Immediate Window: 2 C3,D3 "Tim Zych" wrote: When Areas.Count = 1, the range is contiguous. Dim rng as Range Set rng = Range("C3, D3") debug.print rng.areas.count debug.print rng.Address(0,0) -- Tim Zych SF, CA "ExcelMonkey" wrote in message ... Is there a way in VBA to ascertain if a range selection is contiguous. That is, say I select C3 and D4 on the same page. The Address property for the Selection does not automatically put the address into the form C3:D3 but instead into the form $C$3,$D$3. Is there a way to do this in VBA or do I have to write a function which tests the string for contiguous row or column headers? Sub Macro1() Dim RngAddress As String Range("C3,D3").Select RngAddress = Selection.Address Debug.Print RngAddress End Sub Immediate Window: $C$3,$D$3 Thanks EM |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting proper range address from multiple cell selections
I see what you are saying.
Range("C3:D3") is contiguous Range("C3, D3") is not contiguous as Excel sees it. From the help file, Excel refers to contiguousness as it pertains to the areas.count, and makes a distinction there. To work around that: Sub CountAreas() Dim rng As Range Dim rngArea As Range Dim rngUnion As Range Set rng = Range("C3,D3") Set rngUnion = rng.Areas(1) For Each rngArea In rng.Areas Set rngUnion = Union(rngUnion, rngArea) Next If rngUnion.Areas.Count = 1 Then Debug.Print "Range can form 1 area." Else Debug.Print "More than 1 area - not contiguous." End If 'Debug.Print rngUnion.Areas.Count Debug.Print rngUnion.Address(0, 0) ' or rng.Address(0,0) End Sub -- Tim Zych SF, CA "ExcelMonkey" wrote in message ... The example you show should be a contiguous range but the ouput suggests the opposite. Should this not return a "1" and not a "2"? Sub Address() Dim rng As Range Set rng = Range("C3,D3") Debug.Print rng.Areas.Count Debug.Print rng.Address(0, 0) End Sub Immediate Window: 2 C3,D3 "Tim Zych" wrote: When Areas.Count = 1, the range is contiguous. Dim rng as Range Set rng = Range("C3, D3") debug.print rng.areas.count debug.print rng.Address(0,0) -- Tim Zych SF, CA "ExcelMonkey" wrote in message ... Is there a way in VBA to ascertain if a range selection is contiguous. That is, say I select C3 and D4 on the same page. The Address property for the Selection does not automatically put the address into the form C3:D3 but instead into the form $C$3,$D$3. Is there a way to do this in VBA or do I have to write a function which tests the string for contiguous row or column headers? Sub Macro1() Dim RngAddress As String Range("C3,D3").Select RngAddress = Selection.Address Debug.Print RngAddress End Sub Immediate Window: $C$3,$D$3 Thanks EM |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting proper range address from multiple cell selections
Thanks. That does it. Two last questions
Question 1 I edited the code that when printing to the immediate window is also shows the Sheet name as wel as the cell address. I further want to set it up so that instead of printing results as: Sheet1!C3:D3,F1:G1 It would print as follows: Sheet1!C3:D3, Sheet1!F1:G1 How would you do this? Assuming its the union that bring them together in one line item. Question 2 I am assuming that Union does not work on multiple sheets. How would you do this if you had references that were on multiple sheets? Assuming quick answer is to have separate range objects for each sheet. Is it possible to do it without having separate range objects? Sub CountAreas() Dim rng As Range Dim rngArea As Range Dim rngUnion As Range Set rng = Range("Sheet1!C3,Sheet1!D3,Sheet1!F1,Sheet1!G1") Set rngUnion = rng.Areas(1) For Each rngArea In rng.Areas Set rngUnion = Union(rngUnion, rngArea) Next Debug.Print rngUnion.Parent.Name & "!" & rngUnion.Address(0, 0) ' or rng.Address(0,0) End Sub "Tim Zych" wrote: I see what you are saying. Range("C3:D3") is contiguous Range("C3, D3") is not contiguous as Excel sees it. From the help file, Excel refers to contiguousness as it pertains to the areas.count, and makes a distinction there. To work around that: Sub CountAreas() Dim rng As Range Dim rngArea As Range Dim rngUnion As Range Set rng = Range("C3,D3") Set rngUnion = rng.Areas(1) For Each rngArea In rng.Areas Set rngUnion = Union(rngUnion, rngArea) Next If rngUnion.Areas.Count = 1 Then Debug.Print "Range can form 1 area." Else Debug.Print "More than 1 area - not contiguous." End If 'Debug.Print rngUnion.Areas.Count Debug.Print rngUnion.Address(0, 0) ' or rng.Address(0,0) End Sub -- Tim Zych SF, CA "ExcelMonkey" wrote in message ... The example you show should be a contiguous range but the ouput suggests the opposite. Should this not return a "1" and not a "2"? Sub Address() Dim rng As Range Set rng = Range("C3,D3") Debug.Print rng.Areas.Count Debug.Print rng.Address(0, 0) End Sub Immediate Window: 2 C3,D3 "Tim Zych" wrote: When Areas.Count = 1, the range is contiguous. Dim rng as Range Set rng = Range("C3, D3") debug.print rng.areas.count debug.print rng.Address(0,0) -- Tim Zych SF, CA "ExcelMonkey" wrote in message ... Is there a way in VBA to ascertain if a range selection is contiguous. That is, say I select C3 and D4 on the same page. The Address property for the Selection does not automatically put the address into the form C3:D3 but instead into the form $C$3,$D$3. Is there a way to do this in VBA or do I have to write a function which tests the string for contiguous row or column headers? Sub Macro1() Dim RngAddress As String Range("C3,D3").Select RngAddress = Selection.Address Debug.Print RngAddress End Sub Immediate Window: $C$3,$D$3 Thanks EM |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting proper range address from multiple cell selections
that instead of printing results as:
Sheet1!C3:D3,F1:G1 It would print as follows: Sheet1!C3:D3, Sheet1!F1:G1 Deconstruct the union: For Each rngArea In rngUnion.Areas Debug.Print rngArea.Address(0, 0, , True) Debug.Print rngArea.Parent.Name & "!" & rngArea.Address(0, 0) Next Address has an External argument, giving a fully extended range's address. Not sure if that will help you because it also include the workbook name. Your way is valid too. One thing though (getting picky now, and this is extra info not necessarily relevant to the macro in use): if the sheet has spaces or single quotes in it, there will be a difference between the printed lines above. Say instead of Sheet1, the sheet was names "John's Sheet", the macro will print the following: Debug.Print rngArea.Address(0, 0, , True) '[Book1]John''s Sheet'!C3:D3 Debug.Print rngArea.Parent.Name & "!" & rngArea.Address(0, 0) John's Sheet!C3:D3 See the difference? The first one has opening and closing single quotes, and the single quote in "John's" has been doubled. Since you are setting the range object in the macro in the first place, it assumes there has been a similar adjustment, wrapping single quotes around the sheet names, and doubling up the single quotes within the sheet names, e.g. Set rng = Range("'John''s Sheet'!C3,'John''s Sheet'!D3,'John''s Sheet'!F1,'John''s Sheet'!G1"). Yuck. That is probably not an issue for you, and I debated about whether or not to add what you may think is "too much information" (?).. But your extensive questioning and usage of Addresses makes me wonder if you are going to want to set a range object equal to the address. Another example: Dim SheetName as String Dim rng as Range ActiveSheet.Name = "John's Sheet" SheetName = ActiveSheet.Name ' This works Set rng = Range("'" & Replace(SheetName, "'", "''") & "'!A2") ' This doesn't work: Set rng = Range(SheetName & "!A2") I am assuming that Union does not work on multiple sheets. How would you do this if you had references that were on multiple sheets? Assuming quick answer is to have separate range objects for each sheet. Yep, as far as I know, it's not possible to reference more than one sheet at a time. -- Tim Zych SF, CA "ExcelMonkey" wrote in message ... Thanks. That does it. Two last questions Question 1 I edited the code that when printing to the immediate window is also shows the Sheet name as wel as the cell address. I further want to set it up so that instead of printing results as: Sheet1!C3:D3,F1:G1 It would print as follows: Sheet1!C3:D3, Sheet1!F1:G1 How would you do this? Assuming its the union that bring them together in one line item. Question 2 I am assuming that Union does not work on multiple sheets. How would you do this if you had references that were on multiple sheets? Assuming quick answer is to have separate range objects for each sheet. Is it possible to do it without having separate range objects? Sub CountAreas() Dim rng As Range Dim rngArea As Range Dim rngUnion As Range Set rng = Range("Sheet1!C3,Sheet1!D3,Sheet1!F1,Sheet1!G1") Set rngUnion = rng.Areas(1) For Each rngArea In rng.Areas Set rngUnion = Union(rngUnion, rngArea) Next Debug.Print rngUnion.Parent.Name & "!" & rngUnion.Address(0, 0) ' or rng.Address(0,0) End Sub "Tim Zych" wrote: I see what you are saying. Range("C3:D3") is contiguous Range("C3, D3") is not contiguous as Excel sees it. From the help file, Excel refers to contiguousness as it pertains to the areas.count, and makes a distinction there. To work around that: Sub CountAreas() Dim rng As Range Dim rngArea As Range Dim rngUnion As Range Set rng = Range("C3,D3") Set rngUnion = rng.Areas(1) For Each rngArea In rng.Areas Set rngUnion = Union(rngUnion, rngArea) Next If rngUnion.Areas.Count = 1 Then Debug.Print "Range can form 1 area." Else Debug.Print "More than 1 area - not contiguous." End If 'Debug.Print rngUnion.Areas.Count Debug.Print rngUnion.Address(0, 0) ' or rng.Address(0,0) End Sub -- Tim Zych SF, CA "ExcelMonkey" wrote in message ... The example you show should be a contiguous range but the ouput suggests the opposite. Should this not return a "1" and not a "2"? Sub Address() Dim rng As Range Set rng = Range("C3,D3") Debug.Print rng.Areas.Count Debug.Print rng.Address(0, 0) End Sub Immediate Window: 2 C3,D3 "Tim Zych" wrote: When Areas.Count = 1, the range is contiguous. Dim rng as Range Set rng = Range("C3, D3") debug.print rng.areas.count debug.print rng.Address(0,0) -- Tim Zych SF, CA "ExcelMonkey" wrote in message ... Is there a way in VBA to ascertain if a range selection is contiguous. That is, say I select C3 and D4 on the same page. The Address property for the Selection does not automatically put the address into the form C3:D3 but instead into the form $C$3,$D$3. Is there a way to do this in VBA or do I have to write a function which tests the string for contiguous row or column headers? Sub Macro1() Dim RngAddress As String Range("C3,D3").Select RngAddress = Selection.Address Debug.Print RngAddress End Sub Immediate Window: $C$3,$D$3 Thanks EM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple selections of rows & columns by clicking in one cell | Excel Worksheet Functions | |||
Enabling multiple selections in same cell from dropdown menu | Excel Discussion (Misc queries) | |||
How do I select multiple selections using the range(cell(),Cell()) | Excel Programming | |||
Apply formula to a range with multiple selections | Excel Programming | |||
Creating Multiple GIFs from Multiple Range selections -- I need a volunteer to test my code to see why it fails | Excel Programming |