Excel VBA: Can I simply extend a range by one?

Question

I have a statement in VBA to create a Range, which then is selected and copied. After that I paste that somewhere else. But the last line is pasted without a newline, so I'd like to extend my range by one. My current statement is:

Set twoRange = Range("A2", Range("A2").End(xlDown))
twoRange.Select
twoRange.Copy

What is the simplest way to extend this range one row down?

Answer 1

Yes:

Set twoRange = Range("A2", Range("A2").End(xlDown).Offset(1))
twoRange.Copy

You use Offset to move one more cell down. Offset uses this format: `Offset(Rows,[columns])

You also do not need to select the range to copy the range.

Answer 2

You can do

Set twoRange = Range("A2:A" & Range("A2").End(xlDown).Row + 1)

which adds one to the row found by using the xlDown approach.