# Join ranges in a single one given from comma separated string

#### Fractalis

##### Active Member
Hi. I have this string that represents rows and row ranges separated by comma.

Code:
``str = "1,3,6:8,11:12"``

I want to join those rows in a single range variable (rng). I'm trying with this loop but the issue is initially rng is nothing and the "Union("nothing", Rows(s(0)))" gives error.

Code:
``````    For i = LBound(s) To UBound(s)
Set rng = Union(rng, Rows(s(i)))
Next``````

The complete code I have so far is:

Code:
``````Sub JoinRanges()
Dim str As String, s, rng As Range

str = "1,3,6:8,11:12"
s = Split(str, ",")

For i = LBound(s) To UBound(s)
Set rng = Union(rng, Rows(s(i)))[COLOR=#ff0000] 'Here for first iteration I get error[/COLOR]
Next

rng.Select

End Sub``````

How can be a way to do this?

Thanks for any help.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### JoeMo

##### MrExcel MVP
Code:
``````Sub JoinRanges()
Dim str As String, s, rng As Range
str = "1,3,6:8,11:12"
s = Split(str, ",")
For i = LBound(s) To UBound(s)
If rng Is Nothing Then
Set rng = Rows(s(i))
Else
Set rng = Union(rng, Rows(s(i))) 'Here for first iteration I get error
End If
Next i

rng.Select

End Sub``````

• Fractalis

#### steve the fish

##### Well-known Member
Change the Set line:

Code:
``````    If Not rng Is Nothing Then
Set rng = Union(rng, Rows(s(i)))
Else
Set rng = Rows(s(i))
End If``````

• Fractalis

#### Fluff

##### MrExcel MVP, Moderator
Code:
``````For i = LBound(s) To UBound(s)
If rng Is Nothing Then Set rng = Rows(s(i)) Else Set rng = Union(rng, Rows(s(i)))
Next``````
Or
Code:
``````Set rng = Rows(s(0))
For i = 1 To UBound(s)
Set rng = Union(rng, Rows(s(i)))
Next``````

• Fractalis

#### Fractalis

##### Active Member
Thank you all of you for the fast answer.

You solved my issue!

Very appreaciated.

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback

Replies
3
Views
45
Replies
5
Views
103
Replies
7
Views
35
Replies
0
Views
41
Replies
6
Views
105