CODEWITHSUNDEEP
excelvbavariablessyntax
Handy93
Handy93

Reputation: 17

Right syntax to adress non contiguous Ranges with variables

The goal is to copy a specific non contiguous range e.g.:

 Range("C3, F3, G3").Copy

But with variable rows instead of hard numbers. (variable = i as Long)

Unfortunately the following code:

Dim i as Long
i = 2  
Range("C & i, F & i, G & i").Copy

Does not work and I get a runtime error 1004 (Application defined or object defined error). I suppose because I somehow used wrong syntax here.

Is there any way to address non contiguous ranges with variable rows in VBA?

Many thanks!

Upvotes: 0

Views: 90

Answers (1)

jamheadart
jamheadart

Reputation: 5303

For your current format, you need to put quotation " marks around the string parts, and your variables will join to them, or concatenate, using & outside of the string parts

For example the string "C & i" is read literally by the computer "C & i"

But you want it to read "C2"

So you write it like this "C" & i where i = 2

So for your three cell addresses, you'd use:

Range("C" & i & ", F" & i & ", G" & i).Copy

This would resolve to the string "C2, F2, G2"

You can also do it like this which you might find neater:

Range(cells(i, "C"), cells(i, "F"), cells(i, "G")).Copy

Upvotes: 1

Related Questions