Select Range Help

[Dannyh1]

Hi,

I have the following code

Range(Selection, Selection.End(xlUp)).Select

At the moment this would select Range A100:A1

What I need it to do it select Range A100:A2

Help would be appreciated.

Many thanks,
Danny.

 

[Misca]

The ".End(xlUp))" goes up untill the first nonempty cell (or vice versa).

If your code currently selects A1:A100 it looks like you have selected A100. To make it stop at A2 you can either type something in A2 (if the rest of the range is empty) or clear A1 (if the range is filled with values).

If you want to have your code "shoot up 'till the end and then come down one row" change your code to

Range(Selection, Selection.End(xlUp)).offset(1,0).Select

This only selects A2:A100 if your whole range is either empty or has values - and your active cell happens to be A100.

 

[JamesW]

Hi,

Try:

Range(Selection, "A" & Range(Selection, Selection.End(xlUp)).Row + 1).Select

Or, try from the top down (plus with this you don't have to select a cell):

Range("A2", "A" & Range("A2").End(xlDown).Row).Select

 

[Dannyh1]

Thanks Misca,

However I tried this approah previously, what I get in this situation is range A2:A101 as it offsets the enitre selection

I'm sure offset must come into it by I can't figure out where!

 

[Dannyh1]

Perfect James - thankyou