how to select range

[cazdealer]

Hi,

How could I switch this?

Range("A1:A2500").Select
for something that will select the range finishing at last filled cell instead of A2500.
for exemple, if last cell in column A with data is A3200... this would select A1:A3200


I know there is something with End(xlDown) but I don't know how to write and use it correctly...


thanks

 

[repairman615]

Hello,

<font face=Courier New>Range("A1:A" & Range("A1").End(xlDown).Row).Select</FONT>

 

[cazdealer]

thanks!

 

[repairman615]

Your welcome,

Welcome to the Message Board!

 

[Peter_SSs]

Just be aware that using xlDown could have 2 unintended consequences:

1. If there are any gaps in the data it will not select down to the last filled cell.

2. If it is ever possible that the only cell with data in column A is cell A1, that code will select the whole of column A, not just cell A1.

Both of these possible issues can be avoided by using xlUp instead.*

Range("A1", Range("A" & Rows.Count).End(xlUp)).Select

* This method could also fail if the very last cell in column A is populated, but that would be an extremely rare case and could be dealt with if such a circumstance was possible with your data.

Note also that actually selecting a range is rarely needed in vba and tends to slow your code considerably. As good coding practice, try to avoid selecting where possible.

 

[cazdealer]

Both of these possible issues can be avoided by using xlUp instead.*

Range("A1", Range("A" & Rows.Count).End(xlUp)).Select

*

Thanks for your input Peter,
you're right, xlUp seems to have less possible issues for me.

But something that I liked about the xlDown is that you could do:

Range("A1:C" & Range("B1").End(xlDown).Row).Select

this would select from A1 to the C? but depending on the last row of in column B.

could the one you provided be changed to do something similar but by using xlUp?


thanks

 

[repairman615]

Try:

<font face=Courier New>Range("A1", Range("B" & Rows.Count).End(xlUp).Offset(0, 1)).Select</FONT>

Expands on the example by Peter SSs.