How to select Range using Chr

Question

I need to select the cells "E1 to E4" using the Chr function.

I have been trying to use the following lines of code, but there is some problem with it and the code does not compile.

It is giving the run time error '1004' : Method 'Range' of object '_Global' failed.

Dim num2 As Integer
num2 = 69
Range("Chr(num2) & 1 : Chr(num2) & 4").Select

Please help. Thanks :)

You're missing concats: `"Chr(num2) & 1 & ":" & Chr(num2) & 4"`. But seriously, don't do this - use `.Cells` and numeric indexes instead. You're doing a ton of parsing that Excel is just going to reverse on the other side of the `Range` call. — Comintern, Feb 01 '17 at 05:07
Also, why do you "need" to use `.Select`? It's best to [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) whenever possible. Also, with @Comintern's point, can you expand on your main task/issue? It sounds like we may have an [XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) — BruceWayne, Feb 01 '17 at 05:17
@Comintern too much `"` in your correction. `Chr(num2) & 1 & ":" & Chr(num2) & 4` or `Chr(num2) & "1:" & Chr(num2) & "4"` should work better. But yes, better use indexes. — Vincent G, Feb 01 '17 at 07:50

score 0 · Answer 1 · answered Feb 01 '17 at 08:47

It doesn't work because of incorrect concatenation. I'd recommend storing the range string as a separate variable to help you debug as so:

Sub Test()
Dim num2 As Integer
Dim strRange As String

    num2 = 69
    'Store range string as a variable
    strRange = Chr(num2) & "1:" & Chr(num2) & "4"
    'Pass the variable into the Range function
    Range(strRange).Select

End Sub

How to select Range using Chr

1 Answers1