Dynamic range

[tiredofit]

I would like to set a range based on a series of rows.

For example, the series might be: 1,2,3,6,7,8,11,12,13, ..... , 21,22,23

I don't want to write it all out, ie:

Dim rng As Range
Set rng = Range("A1:A3, A6:A8, A11:A13, A16:A18, A21:A23")

because my series could be very long.

Is there a way to set a range by incorporating a For Next Loop, for example:

' pseudocode

Dim rng As Range

Dim i As Integer, j As Integer

For i = 1 To 21 Step 5

    For j = i To i+2

        Set rng = rng & "A" & j

    Next j

Next i

Thanks

 

[RoryA]

Yes - use Union

 

[tiredofit]

Yes - use Union

Thanks, can you show me the correct syntax?

 

[RoryA]

Something like:

For i = 1 To 21 Step 5

   If rng is nothing then
      Set rng = Cells(i, "A").Resize(3)
   else
      Set rng = Union(rng,  Cells(i, "A").Resize(3))
   End If

Next i

 

[tiredofit]

Something like:

For i = 1 To 21 Step 5

   If rng is nothing then
      Set rng = Cells(i, "A").Resize(3)
   else
      Set rng = Union(rng,  Cells(i, "A").Resize(3))
   End If

Next i

Brilliant, thanks a lot.