0

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?

shaolincss
  • 3
  • 1
  • 3
  • You might be meaning the “shortcut notation” from here, but I’d encourage you to check out all of the links here https://learn.microsoft.com/en-us/office/vba/excel/Concepts/Cells-and-Ranges/reference-cells-and-ranges – Marcucciboy2 Aug 26 '18 at 00:42

1 Answers1

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
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    Thank you for your answer, but I really Am using select as an example. the command I will be using is Copy. And I don't really understand the notation that you used. I'm used to a secuential notation like this Sheets(2).Range("E45,F45,G45,H45,I45,J45").Copy Sheets(4).Cells(i + 3, 21).PasteSpecial xlPasteValues Sheets(2).Range("E15:E20").ClearContents – shaolincss Aug 25 '18 at 17:46
  • 1
    so then: `Union(Sheets(2).Cells(1,1),Sheets(2).Cells(1,3),Sheets(2).Cells(1,6),Sheets(2).Cells(1,8)).Copy` – Scott Craner Aug 25 '18 at 17:50
  • 1
    Using the `With` keeps from having to type the sheet name multiple times. – Scott Craner Aug 25 '18 at 17:50
  • 1
    Evoking the "Cells" property of a "Range" object in VBA is not usually described as the use of the "r1c1" notation. Please check this link, for example, for the r1c1 reference: " http://www.meadinkent.co.uk/excel_r1c1.htm ". And ".Range" is not a command either... Just saying... –  Aug 25 '18 at 21:01
  • @Gene you are correct of the op wants to change to R1C1 o. The sheet the can use that on the sheet. Vba still requires A1 reference in Range, unless on does some fancy function s to change on the fly. Also where do I state range is a command? – Scott Craner Aug 25 '18 at 21:26
  • Ahh! This was not even meant for you, Scott. Perhaps it's more confusion on my part with the "Subject" line... Sorry. Maybe it would be prudent to consider changing it to a more clear one, something along the lines of "Selecting a Range comprised of multiple non-adjacent cells using .Cells() notation" –  Aug 26 '18 at 13:29
  • thx you all for the responses. I solved it using union as @ScottCraner said. I did this `With Sheets(1) Union(.Cells(2, 2), .Cells(2, 4), .Cells(2, 6), .Cells(2, 8), .Cells(2, 10)).Copy Sheets(3).Cells(i + 3, 2).PasteSpecial xlPasteValues 'Union(.Cells(2, 2), .Cells(2, 4), .Cells(2, 6), .Cells(2, 8), .Cells(2, 10)).ClearContents End With` – shaolincss Aug 26 '18 at 22:43