Using a variable in a range statement in VBA excell macro

Question

Ok, I'm sure this question is really basic but all my searchers turn up complicated answers and I've been messing with VBA for about a day. I have two worksheets in an excel doc, I've created a button that I can click that invokes my macro that is just moving cells from one work sheet to another. But I need my macro to determine what row I am on. I'm using this:

r = ActiveCell.Row

to determine my row, but what would be the easiest way to use that variable in a range statement like this:

Range("A2").Select

Please clarify what you're asking, though. The way I read is: "how do I select the row from the variable r?" but it's not clear that is really your question. — David Zemens
– David Zemens, Commented Feb 20, 2015 at 17:25

tigeravatar · Accepted Answer · 2015-02-20 17:24:20Z

8

You could use the Range method with the & operator to join the variable into a string:

Range("A" & r)

Alternately you can use the Cells method which takes arguments (Row, Column):

Cells(r, "A")

answered Feb 20, 2015 at 17:24

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

Using a variable in a range statement in VBA excell macro

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related