How to use a variable with Range?

Question

I want to use the Rowcount as the second Parameter for the Range.

Set R = Range("A1", Range("A" & Rows.Count).End(xlUp))

Range("N2":R).Value =...

How is it possible to use R here as the second Parameter?

Best regards!

@Zac I don't think the above will work, R is a range here. Perhaps this will Range("N2:N" & R.Rows.Count).Value =... — tsdn
– tsdn, Commented May 9, 2018 at 9:28

FishermansFriend · Accepted Answer · 2018-05-09 09:58:35Z

1

Set R = Range("A1", Range("A" & Rows.Count).End(xlUp))
Range("N2:" & R.Address).Value =...

answered May 9, 2018 at 9:58

FishermansFriend

529 bronze badges

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

Comments

Kazimierz Jawor · Accepted Answer · 2018-05-09 09:27:25Z

0

Can look as a more complicated but is logical:

Set R = Range("a1", cells(rows.count, "A").end(xlup))
range("N2", cells(r.rows.count, "N")).value=...

Step-by-step solution:

Set lastA = cells(rows.count, "A").end(xlup)
set R = range("A1", lastA)

range("N2", cells(lastA.row, "N")).value = ...

answered May 9, 2018 at 9:27

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Comments

Karthick Gunasekaran · Accepted Answer · 2018-05-09 09:49:09Z

0

pls try with below

Sub test()
    Dim r As Range
    Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))
    Range("N2:N" & r.Rows.Count).Value = "R"
End Sub

edited May 9, 2018 at 9:49

answered May 9, 2018 at 9:29

Karthick Gunasekaran

2,7231 gold badge17 silver badges26 bronze badges

4 Comments

Marius Over a year ago

Thanks for the answer, but I want to use "r" just for the second value of the Range, not for the whole

Karthick Gunasekaran Over a year ago

you want the second value of r range (i.e A2) in N2?

Marius Over a year ago

the solution above is perfectly working, thank you though!

Karthick Gunasekaran Over a year ago

@Marius, Good to hear. Thanks

Collectives™ on Stack Overflow

How to use a variable with Range?

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related