Set range in VBA

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,039
Office Version
  1. 365
Platform
  1. Windows
Hello,

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

Code:
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.

Code:
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.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
I would be written like this:

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

Which isn't really condensed any more.

Dom
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Don't think you can condense any further.

You might prefer one of the below versions though:

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

Not really any shorter though.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,039
Office Version
  1. 365
Platform
  1. Windows
I figured as much. Just curious. Thanks for the replies though.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,383
Messages
5,601,318
Members
414,441
Latest member
KellyTheKid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top