Hi. As a side note, this was on my "ToDo" list for my Library, so let
me do it now.
If interested, instead of writing rows as "1:1, 3:3, 5:5"
here is a technique to write it as "1,3,5"

In Vba, you need to set a library reference to "BScript_RegExp_55"

Sub Main()
Dim R(1 To 3) As String
Dim BigRng As Range

Dim s As String
R(1) = "1,3"
R(2) = "7,10,13"
R(3) = "15"

Set BigRng = Range(ToRows(Join(R, ",")))
BigRng.Select
End Sub

Function ToRows(s) As String
' = = = = = = = = = = = = = = =
'// By: Dana DeLouis
'// VBA Library Ref to VBScript_RegExp_55
'// Input: Rows as single digits
'// ie "1,3,5"
'// Returns Proper Row Reference
'// ie "1:1, 3:3, 5:5"
' = = = = = = = = = = = = = = =


With New RegExp
.Pattern = "(\d+)"
.Global = True
ToRows = .Replace(s, "$1:$1")
End With
End Function

= = = = = = = = = =
HTH :)
Dana DeLouis



On 3/24/2010 9:26 AM, The Flash wrote:
Thanks guys!

"Dana DeLouis" wrote:

Set BigRange = Application.Union ( Range (R(1)), Range (R(2)),
Range(R(3)) )

Hi. As a side note, you do not have to type in each of the indexes.
However, if you really do have "Hundreds" of indexes, then I think there
'might' be a size limit to this technique. Not sure. ??

Sub Demo()
Dim BigRange As Range
Dim R(1 To 3) As String

R(1) = "1:1, 3:3"
R(2) = "7:7, 20:20, 43:43"
R(3) = "100:100"

Set BigRange = Range(Join(R, ","))
BigRange.Select
End Sub

HTH
Dana DeLouis


On 3/23/2010 3:51 PM, The Flash wrote:
From what I understand you must use Application.Union to combine large Range
groups in vba to use in Excel.

Somthing like...

Dim BigRange as Range, R(10) as String
R(1) = "1:1, 3:3"
R(2) = "7:7, 20:20, 43:43"
R(3) = "100:100"

Set BigRange = Application.Union ( Range (R(1)), Range (R(2)), Range
(R(3)) )
BigRange.Select


Which is fine and dandy with just the 3, but if I had a hundred or so ranges
how would I create a loop statment to combine the various ranges.

*The ranges would contain the entire row of a worksheet and only values from
the first column that contain the text "PICK ME!" would be selected.


Any help is much appreciated!
.



--
= = = = = = =
HTH :)
Dana DeLouis