Set a range using variables in Excel vba

Question

I just want to use a variable as part of a range in Vba to select some data. I looked into the forum and found similar posts, however none solved my problem.

I defined MyVar as:

MyVar = Sheets("working").Range("CQ3").Value

In this case MyVar = 5 and I want to add 1 because I'm skipping the headers on sheet. Now I want to select from A2 to Column CN and use as Row MyVar + 1

so far I tried:

Sheets("working").Range("A2: CN & MyVar + 1").select

Sheets("working").Range("A2": CN "& MyVar + 1").select

Sheets("working").Range("A2": "CN & MyVar + 1").select

Sheets("working").Range("A" & 2: "CN" & MyVar + 1).select

and none is working

Thank you very much in advance for your appreciated support

Here is the code:

MyVar = Sheets("working").Range("CQ3").Value



Sheets("working").Range("A2: CN & MyVar + 1").select
Sheets("working").Range("A2": CN "& MyVar + 1").select
Sheets("working").Range("A2": "CN & MyVar + 1").select
Sheets("working").Range("A" & 2: "CN" & MyVar + 1).select


Selection.copy
Sheets("TOTALS").Select
Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Michał Turczyn · Accepted Answer · 2019-09-09 06:07:17Z

1

Just use:

Sheets("working").Range("A2:CN" & (MyVar + 1)).Select

Additional brackets force the order of operations, so first MyVar + 1 is calculated, then the result is appnded to a string indicating the range address.

Also I'd avoid Select.

edited Sep 9, 2019 at 6:07

answered Sep 9, 2019 at 5:52

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

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

Comments

Pᴇʜ · Accepted Answer · 2019-09-09 05:56:27Z

0

The correct syntax is:

Worksheets("working").Range("A2:CN" & (MyVar + 1)).Select

The constant part of your range address needs to be in enclosed in quotes "" and the variable part needs to be concatenated with &. To ensure the calculating + 1 is done before concatenating you can enclose the variable part in parenthesis ().

And you might benefit from reading How to avoid using Select in Excel VBA.

answered Sep 9, 2019 at 5:56

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Collectives™ on Stack Overflow

Set a range using variables in Excel vba

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related