Defining a range using a variable integer in VBA

Question

So here is my problem. I know we can define a range in VBA, say for example like Range("A1:A300"). Now say I have some integer x. I want the range from A1 to Ax. How can I write this in a code? Range("A1:Ax") clearly does not work

Oh gee thanks a a lot for the down vote just say it's a duplicate and I would have deleted the question — Heisenberg
– Heisenberg, Commented Feb 3, 2015 at 8:53
GSerg, that "dupe", by the way, is way over-complicated for this specific need. It also won't handle AA thru whatever the current maximum column is :-) — paxdiablo
– paxdiablo, Commented Feb 3, 2015 at 8:55
@paxdiablo I did not vote for close as duplicate, otherwise there would be a close vote on the question. The question is not about Excel or ranges, it is about concatenating a string and a number. This question, while arguably not welcome on SO considering all the "please get a book on the language basics" comments these questions have generated, has been answered plenty of times, I simply dumped a link to the first one I found that shows the technique, with the added bonus of being in Excel context. — GSerg
– GSerg, Commented Feb 3, 2015 at 9:24

Alex K. · Accepted Answer · 2015-02-03 12:24:46Z

2

You can specify the anchor cell then extend:

range("A1").resize(300)

answered Feb 3, 2015 at 12:24

Alex K.

177k32 gold badges276 silver badges299 bronze badges

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

Comments

paxdiablo · Accepted Answer · 2015-02-03 08:48:46Z

1

That would be something like:

Range("A1:A" & CStr(x))

The CStr function will turn an integer into a string though I think you can actually just leave it off in later versions of Excel:

Range("A1:A" & x)

answered Feb 3, 2015 at 8:48

paxdiablo

889k243 gold badges1.6k silver badges2k bronze badges

Collectives™ on Stack Overflow

Defining a range using a variable integer in 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