0

I would like to add multiple Cells through the .Cells(x,y) Syntax to a Range named "ValueRange" like so:

Dim ws As Worksheet
Dim j As long
Dim Valuerange As Range

j = 6 'rowNumber 6
Set ws = ThisWorkbook.Worksheets("My Worksheet1")
Set Valuerange = ws.Range(ws.Cells(j, 3), ws.Cells(j, 5), ws.Cells(j, 7), ws.Cells(j, 9))

and I would like to Select this range through the same format later after changing j (rowNumber)

ws.Range(ws.Cells(j, 3), ws.Cells(j, 5), ws.Cells(j, 7), ws.Cells(j, 9)).Select

But I always get errors with this Syntax "Wrong Number of Arguments or invalid property assignment"

Can anyone help? Cheers

Leon
  • 17
  • 5
  • 1
    The `Range` object takes two arguments: upper right and lower left corners of the range. If you want to refer to non-contiguous ranges, you will need to `Union` the several contiguous range objects. Also, why are you using `Select`? It is rarely necessary, and frequently leads to problems. See [How to avoid Using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) (*it is an easily understood mistake as the macro recorder usually includes multiple selects and activates*) – Ron Rosenfeld Mar 26 '21 at 11:03
  • `Set Valuerange = Union(ws.Cells(j, 3), ws.Cells(j, 5), ws.Cells(j, 7), ws.Cells(j, 9))` – FunThomas Mar 26 '21 at 11:05
  • Two expressions on why you thought it may work (but it doesn't): `Set Valuerange = ws.Range("C6,E6,G6,I6")` hence `Set Valuerange = ws.Range("" & ws.Cells(j, 3).Address & "," & ws.Cells(j, 5).Address & "," & ws.Cells(j, 7).Address & "," & ws.Cells(j, 9).Address & "")`. The parameter has to be a string. Test each of them with `Debug.Print Valuerange.Address`. – VBasic2008 Mar 26 '21 at 11:39
  • Thanks you guys! Ron explained it perfectly and I got it to work with the Union Statement from Thomas. Can I Check an answer even though its a comment? Thank you all! – Leon Mar 26 '21 at 11:59

1 Answers1

0

See comments of question for easy answer through using a Union-Statement instead of Range-Object

Leon
  • 17
  • 5