Non-Sequential Range

Can I set a Range Object to be equal to a set of non-sequential cells? I want my Range to be equal to cells A1, A3, A5 etc, where I can pick and choose the rows and columns at will.

From stackoverflow Chris Gunner

• Sure, for example this selects a fairly random set of cells:

  Range("A1:C8,E13:G21,C20:D26,L13:N22").Select
  

  and this selects the cells you mentioned:

  Range("A1,A3,A5").Select
  

  You can also combine Range objects with the Union method.

  Tomalak : But why "Select()"?

  andynormancx : The select was simply an easy way to demonstrate visually that it works.
  From andynormancx

• From MSDN:

  Union Method

  Use Union(range1, range2, ...) to return multiple-area ranges — that is, ranges composed of two or more contiguous blocks of cells. The following example creates an object defined as the union of ranges A1:B2 and C3:D4, and then selects the defined range.

  Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
  Worksheets("sheet1").Activate
  Set r1 = Range("A1:B2")
  Set r2 = Range("C3:D4")
  Set myMultiAreaRange = Union(r1, r2)
  

  From Mike Woodhouse