UsedRange in a Selection

Wondering it's possible to use the UsedRange property within a selection. For instance, if I have a long list of Data in column A, and select all of column A, but want my macro to only operate on the cells with values in my selection, can I use the UsedRange property, or is there a better way? This comes up because I was using my little macro below and accidentally selected the whole column, so the macro took 55 seconds to run, when normally it runs very quickly.

Please Login or Register  to view this content.

If you use select, you need to select the range manually.

Please Login or Register  to view this content.

I think I solved it using intersect, plus bringing in davesexcel's excellent suggestion. let me know if you see anything that can be improved upon with my code below.

Please Login or Register  to view this content.

So then this would be an equivalent macro?

Please Login or Register  to view this content.

Mike,
Thanks for the warning. I fixed as shown below.

Please Login or Register  to view this content.

I liked your example macro, but slightly reworked it for dummies like me that have to see what's happening to understand the problem.

Please Login or Register  to view this content.

I guess I didn't explain the purpose of the macro. Suppose JoeBob sends some data, and one section/colum/row/range area has the values reversed (this happens to me all the time), so that range needs to be converted. Easiest way is to select the range to convert and hit the "Easy" button (ie run my PlusMinus macro).

If my range size was always the same I wouldn't do any selecting, I'd just load the macro with the proper range.

I COULD have an interface asking for the range to convert, but that involves an extra step for the user, and accomplishes no purpose that couldn't be more easily done by simply selecting the range to convert and running the macro. The whole idea is a quick and easy conversion of whatever range the user needs to convert.

Just realized you may have been talking about the changes I did to your macro.

To me, to demonstrate something, you want to show it, so to show what's happening you either need 1000 words, or a picture. So in Test I stop the macro so the user (Me, in this case) can see what's happening. When you can see that only A4 is selected, yet the msgbox displays "$A$1:$A$4,$A$6:$A$10", it's an easy picture to see that there's a problem.

I agree wholeheartedly. It was a wonderful day for me when I realized I could do magic without "touching" anything on my sheets.