0

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
  • 32,028
  • 14
  • 47
  • 69
Gabo Walker
  • 95
  • 9

2 Answers2

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.

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73