Auto-Sort with multiple Dynamic Ranges

gidge223

New member
Joined
Oct 5, 2017
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2016
Hello Everyone,

First off, thank you to those who spend their time to analyze and provide solutions to everyone's questions on this website!

My Goal:
Use a pushbutton to auto-sort multiple dynamic ranges of rows.

Clarification:
To explain this, I have attached an example spreadsheet. I want to "organize", or sort, the rows that contain priority numbers in each section. So, if I were open this document, I would want:


  1. A button located near the test "Section 1" that when clicked, only sorts rows 4 - 6 by priority level (1 = high priority, 2 = lesser priority, etc.).
  2. Then, another button located near the test "Section 2" that when clicked, only sorts rows 8 - 13 by priority level.
  3. Then, another button located near the test "Section 3" that when clicked, only sorts rows 15 - 17 by priority level.
  4. Then, another button located near the test "Section 4" that when clicked, only sorts rows 19 - 20 by priority level.

Problem:
Understand, that I know I can use the "Custom Sort..." feature in Excel to accomplish sorting, but that is a lot of clicking and typing when I want to make the document more user-friendly. I am also able to do this with Macros and it would be fine if the number of rows in each section never changed, but I am constantly inserting rows to each of the sections. When I add a row to each section and then initiate the button with the assigned Macro, it only sorts the original (sections without newly inserted cells) specific ranges. So for instance under "Section 2", I have priority numbers located in rows 8 - 13. When I insert a row to add another priority number and the rest of information for that row and use the macro, it only sorts rows 8 - 13, but not rows 8 - 14 (which includes the newly added row).

How do I modify the Macro or use VBA code to automatically adjust the size of the range, so that when more rows are added to each section, all rows under each section (new and old) are sorted?

Solution?

Thank you to those in advance who contribute to this thread.
 

Attachments

  • Sorting Example.xlsx
    9.2 KB · Views: 5
Hi gidge223,

Not sure if you're willing to try non VBA methods, but you can use Power Query and do exactly what your stated goal is.
 
Back
Top