Join ranges in a single one given from comma separated string

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
252
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.
 

Some videos you may like

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
Joined
May 26, 2009
Messages
17,211
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
How about
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

Active Member
Joined
Oct 11, 2011
Messages
252
Thank you all of you for the fast answer.

You solved my issue!

Very appreaciated.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,032
Messages
5,526,360
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top