Recording excel macro that sorts a dynamic range

[guitarzan]

When I record a macro in Excel that includes sorting, the macro always hard-codes the cell range. So if I run the macro on another spreadsheet with more rows, it only sorts the top portion. Now, I know how to solve this with some VBA code, but I'm trying to demonstrate the value of recording macros to some people who do not know VBA... things like resizing columns, adding Autofilter, change formatting in columns, etc. The spreadsheets they would be working with would all have the same columns, but the number rows can change. Is there any way to record a macro that can be repeated, but will always sort all the rows without the addition of VBA code?

 

[combo]

The excel macro recorder is a long existing tool that sometimes does not cope with all new features MS implements in newer versions - some actions are not recorded. It is window oriented, so it frequently uses Selection, ActiveWorkbook or ActiveSheet. The code generated is rather a support source than final product.
Concerning macro recorder and sorting, if you create structured table first, recorded macro refers to table name and column names, without coding classic addresses. Another nice feature of structured tables.

combo

 

[guitarzan]

Thanks combo. Yes, I was hoping some workaround would have been possible for generic sorting, but it seems not!

 

[SkipVought]

I often use the macro recorder. But I ALWAYS need to customize the recorder results.

Learn how to use Structured Tables, CurrentRegion, UsedRange.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[guitarzan]

Thanks Skip. Yes I agree, but I was looking for a simplest possible method using macro recording only. Depending on what cells are selected, Excel sometimes will ask if you want to "expand the selection" when you try to sort. I tried that and several other ways, but no matter what it always seems to hard code the rows in the resulting macro. Even hitting CTRL-A (which records as Cells.Select) doesn't help. I was hoping I missed something but I guess not!

 

[SkipVought]

My experience in the aircraft manufacturing industry, they have and use MRP/MRPII/ERP systems, but rely heavily on ad hoc reporting in Excel to help analysis and manipulate data for scheduling decisions.
Over a 25 year span I saw lots of way under-powered and over-maintained spreadsheets. Initially I increased the power and decreased the maintenance of many of them. Then I got interest by some in learning how to up the power and down the hands-on month after month. So I began brown-bag lunch Excel training. There were scant few who stuck, because it takes time and effort to gain proficiency.
Hope you have better luck than I.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein