Select individual cells with r1c1 notation in a .Range command

Question

I'm trying to select individuals cells with r1c1 notation but I don't know how, and so far I've found how to do it. What I want to achieve is something like this

Sheets(1).Range("A1,C1,F1,H1").Select but I want to do this with the r1c1 notation.

Sheets(1).Range(Cells(1,1),Cells(1,3)...).Select but with this I can only select a range between the to cells, and I can't add more than two without an error

is there a way to do this with the range command and c1r1 notation?

Answer 1

Use Union:

With Worksheets("Sheet1") 'Change to your sheet name.
    Dim Rng as Range
    Set rng = Union(.Cells(1,1),.Cells(1,3),.Cells(1,6),.Cells(1,8))
    Debug.print rng.Address(0,0)
End With

Notes:

1. One should always assign parentage to all range objects.
2. The use of .Select should be discouraged, It can be avoided in 99% of the time. See: How to avoid using Select in Excel VBA