Excel VBA Range variable concatenation

Question

I Have the below Chunk of Excel VBA Code . Now I'll have to put them In a Loop & Parametrize them.

Countruz2 = 2
 Countruz3 = Countruz2 + 13

Range("A2:A15").PasteSpecial (xlPasteValues)

Below is what I've tried - 3 variants all failing with: Invalid use of Property Error.

'    Range ("A" & Countruz2 & ":" & "A" & Countruz3)                                    ' Invalid Use of Property

'    Range ("A" & Countruz2 & ":A" & Countruz3)                                         ' Invalid Use of Property

'    Range ("A" & Countruz2 & ":A" & Countruz2 + 13)                                    ' Invalid Use of Property

How do we deal with Such Concatenations & Parametrizations within the Range function in Excel VBA ?

Thanks for all the help in advance.

Edit :

Got it working after concatenating it like this -

X = "A" & Countruz2 & ":" & "A" & (Countruz2 + 13) Debug.Print X Range(X).PasteSpecial (xlPasteValues)

Any other elegant / simpler way?

You only need the top-left cell for the destination of a paste. Anything else is overkill and just another possible error when you get the destination size or orientation wrong. — user4039065
– user4039065, Commented Jul 10, 2018 at 19:12

YasserKhalil · Accepted Answer · 2018-07-10 18:26:08Z

1

Try this

Countruz2 = 2
Countruz3 = Countruz2 + 13

Range("A" & Countruz2 & ":A" & Countruz3).PasteSpecial (xlPasteValues)

answered Jul 10, 2018 at 18:26

YasserKhalil

9,60813 gold badges62 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA Range variable concatenation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related