select range within macro

Posted by Chris on December 10, 2001 8:18 AM

I have a sheet which I often resort on col A or col B, so I do this with macro's attached to buttons. But when I add more data to the sheet, I have to respec the macro to sort to the new bottom of the sheet. How can I select all the non-blank cells within a macro?
Any theories gladly accepted

Posted by Tom Urtis on December 10, 2001 12:36 PM

If you are wondering how to select a dynamic range among columns A:B, here's one suggestion. It assumes your data starts in cell A2.
If this is not what you want, please repost:

Range(("A2"), Range("B65536").End(xlUp)).Select

HTH
Tom U.

Posted by chris b on December 11, 2001 10:56 AM

Thanks Tom. That goes some of the way, but will leave me
having to edit the macro whenever I add more data to the
sheet. I really want the macro to pick up all the data
down to the first blank cell without me having to input
a reference as to where that is.

thanks

chris

Posted by Tom Urtis on December 11, 2001 11:12 AM

This macro does what you want then, by selecting the range no matter how many rows you add to it. Run the code, then add more rows to your range, and run it again. It should select the entire data-occupying range each time.
Maybe I am misunderstanding what you want, so please repost if I am answering a question you are not asking.

Tom U.

Posted by chris b on December 12, 2001 12:37 PM

Tom;
No, you understand me better than I do. I didn't recognise the correct
answer when I saw it - its so simple! Thanks for the help
Chris