VBA: How to use 'xltoright' and 'xldown' without using the selection method

Question

I am trying to speed up my macro by eliminating the use of the select method. Much of my code has code like this:

    Range("A5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select

how can I do the same thing without using select?

What do you do with this selection? Seems like you need to [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and similarly find the last column? — BigBen, Jul 10 '20 at 14:57
@DStanley - [because it is](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba)? — BigBen, Jul 10 '20 at 14:57
@BigBen I am using this selection to sort my data. Depending on what happens earlier in the macro this range can change. This is why I was using the xltoright and xldown — brwnsfan, Jul 10 '20 at 15:02
One option is to use `Range.CurrentRegion`. A better option in my opinion is to find the last row (and similarly the last column) using the approach in the linked question from my first comment. — BigBen, Jul 10 '20 at 15:03

D Stanley · Answer 1 · 2020-07-10T15:00:44.010

2

You can define the range without selecting it:

Range("A5", Range("A5").End(xlToRight).End(xlDown))

edited Jul 10 '20 at 15:00

answered Jul 10 '20 at 14:58

D Stanley

1 Answers1