using range function excel with variables

Question

I would like to use variables within the range function, to assign adaptive range selection in excel VBA.

Range("A"&aa+4":C5"&bb+4).Select

where aa and bb are variables.

Thanks !

youre missing an additional & after the first 4 to have a proper concatenation. And please try to avoid using .Select you will find many resources on this with a quick search — user2140173
– user2140173, Commented Mar 5, 2014 at 16:10

MLDev · Accepted Answer · 2014-03-05 16:22:03Z

2

Try using this:

Range("A" + Strings.Trim(Str(aa + 4)) + ":C" + Strings.Trim(Str(bb + 4))).Select

or this:

Range(Cells(aa + 4, 1), Cells(bb + 4, 3)).Select

Also there is an article I've written on my blog about the different methods of referencing ranges in excel using VBA which covers this topic. Referencing Ranges in Excel Using VBA

edited Mar 5, 2014 at 16:22

answered Mar 5, 2014 at 16:16

MLDev

1,2774 gold badges13 silver badges23 bronze badges

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

Comments

Gary's Student · Accepted Answer · 2014-03-05 16:22:43Z

0

This little sub:

Sub dural()
    aa = 7
    bb = 11
    Range("A" & aa + 4 & ":C5" & bb + 4).Select
    MsgBox Selection.Address
End Sub

will produce:

$A$11:$C$515

answered Mar 5, 2014 at 16:22

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

using range function excel with variables

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