Another Sort Macro please

[lapta301]

Hi All

I have an ever changing block of data that including headers covers the range A1 to AM500

Column A will always have data in it but AM column will not always but it will be the final column in the range.

What I would like is a macro that will sort the range from A1 to whatever the final row is and along to column AM by the values in column B.

But there is a complication.

There is another block of data that will be below the range that I want to sort so the code will need to go down from A1 to find the last row rather than coming up from the bottom.

I hope that this makes sense.

As ever your help is most appreciated.

 

[tusharm]

One simple way to identify the region around A1 that has data is to use range("a1").currentregion.

Hi All

I have an ever changing block of data that including headers covers the range A1 to AM500

Column A will always have data in it but AM column will not always but it will be the final column in the range.

What I would like is a macro that will sort the range from A1 to whatever the final row is and along to column AM by the values in column B.

But there is a complication.

There is another block of data that will be below the range that I want to sort so the code will need to go down from A1 to find the last row rather than coming up from the bottom.

I hope that this makes sense.

As ever your help is most appreciated.

 

[lapta301]

tusharm

Thanks for pointing me in that direction I'll give it a try shortly.

Regards

lapta301

 

[lapta301]

tusharm

That works great thank you.

However in thinking about how often I can use this I can see a minor problem on the horizon.

So if for example the currentregion is A1 to AM500 is there a way to take a row off and make it 499 or whatever.

Many thanks

 

[tusharm]

You can always resize a range as required. Check out Excel VBA help for Resize.

tusharm

That works great thank you.

However in thinking about how often I can use this I can see a minor problem on the horizon.

So if for example the currentregion is A1 to AM500 is there a way to take a row off and make it 499 or whatever.

Many thanks

 

[lapta301]

tusharm

Thank you again for directions which again work a treat.

Kind regards from a sometimes very wet and sometimes very sunny Southern England.

 

[tusharm]

tusharm

Thank you again for directions which again work a treat.

Kind regards from a sometimes very wet and sometimes very sunny Southern England.