Set range in VBA

[lrobbo314]

Hello,

I use the following method to set ranges in VBA, this code will set the range = A2:Q25 with my data.

Set r = Range(Range("A2"), Range("Q" & Rows.Count).End(xlUp))

I thought that I might be able to condense this code even further but this following line of code results in setting the range to A1 only.

Set r = Range("A2:Q" & Rows.Count).End(xlUp)

I've tried searching but can't find anything about this.

I know that I can set an integer = the last row in the column, but I am tring to find a way to keep it to one line of code.

I'd just like to know for best practices sake. In the scheme of things I know that that first line is fine.

Thanks for any input.

 

[Domski]

I would be written like this:

Set r = Range("A2:Q" & Range("Q" & Rows.Count).End(xlUp).Row)

Which isn't really condensed any more.

Dom

 

[njimack]

Don't think you can condense any further.

You might prefer one of the below versions though:

Set r = Range(Cells(2, 1), Cells(Rows.Count, 17).End(xlUp))

Set r = Cells(2, 1).Resize(Cells(Rows.Count, 17).End(xlUp).Row, 17)

Not really any shorter though.

 

[lrobbo314]

I figured as much. Just curious. Thanks for the replies though.