How do I programmatically select various ranges in vba?

[The Flash]

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!

 

[Lars-Åke Aspelin]

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!

Try this as an alternative to you Set statement

Set BigRange = Range(R(1))
For i = 2 To 100
Set BigRange = Application.Union(BigRange, Range(R(i)))
Next i

Hope this helps / Lars-Åke

 

[tompl]

I am not sure that Application Union is necessary. I wrote the following to
select all of the rows in the active worksheet that have "Pick Me!" in column
A. I hope that is what you had in mind.

Sub mcrSelect_Pick_Me()

Dim rngRow As Range
Dim strAll As String

For Each rngRow In ActiveSheet.UsedRange.Rows
If Range("A" & rngRow.Row) = "Pick Me!" Then
strAll = strAll & rngRow.Row & ":" & rngRow.Row & ","
End If
Next rngRow

strAll = Left(strAll, Len(strAll) - 1)
Range(strAll).Select

End Sub

tom

 

[tompl]

Nice Lars.

Here is an alternative that does not require the program to identify the
first range to be set "manually".

Sub mcrSelect_Pick_Me2()

Dim rngBig As Range
Dim rngRow As Range

For Each rngRow In ActiveSheet.UsedRange.Rows
If Range("A" & rngRow.Row) = "Pick Me!" Then
If rngBig Is Nothing Then
Set rngBig = rngRow
Else
Set rngBig = Application.Union(rngBig, rngRow)
End If
End If
Next rngRow

rngBig.Select

End Sub

 

[Dana DeLouis]

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

 

[The Flash]

Thanks guys!

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



.

 

[Dana DeLouis]

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