×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Recording excel macro that sorts a dynamic range

Recording excel macro that sorts a dynamic range

Recording excel macro that sorts a dynamic range

(OP)
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?

RE: Recording excel macro that sorts a dynamic range

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

RE: Recording excel macro that sorts a dynamic range

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

RE: Recording excel macro that sorts a dynamic range

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

Learn how to use Structured Tables, CurrentRegion, UsedRange.



Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Recording excel macro that sorts a dynamic range

(OP)
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!

RE: Recording excel macro that sorts a dynamic range

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close