Sort more than one range?

[TheNewGuyFromPA]

Hi folks,
I am very new to VBA and have been pecking through it clumsily.
I've created this macro to sort a range:

_________________________________________________________________

Please Login or Register  to view this content.

__________________________________________________________________

It works perfectly. But now I need a new range to sort along with A4-I141.
I simply don't know how to add this range into the mix.
I've tried a union but I must have syntax wrong because it throws me errors.
I am sure there's an easy way to include cell range AF4:AP141 so it will sort along with A4-I141.
I would assume defining the new range is part of the process but I don't know the proper way to do this.
Can someone help a rookie VBA guy with an easy way to add this new range into the mix?

Thanks!

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. As you are new here, I shall do it for you this time.)

[TheNewGuyFromPA]

Sorry I'm very new here and still working through proper procedures.

Please Login or Register  to view this content.

[rorya]

You can't sort two separate ranges at the same time (either manually or in code). Is there a particular reason the range in between shouldn't sort?

[TheNewGuyFromPA]

The code I have above is working fine.
I need an additional range to sort along with the range A4:I141.
In other words, range AF4:AP141 need to line up with the original range when the sort happens.
When sorting, say the value in A4 ends up in A50.
I would need AF4 to end up in AF50 as well.
I just don't know how to add another range into the mix.

[AliGW]

Sorry I'm very new here and still working through proper procedures.

Just have a read of the rules (it's all there): https://www.excelforum.com/forum-rul...rum-rules.html

[rorya]

As I said, that won't work. I'd suggest you either rearrange your worksheet so that the part that needs to sort is all in one block, or post a copy of your workbook so that we can figure out the best alternative.

[TheNewGuyFromPA]

I've attached a sample sheet.
In the sample I want to sort A2:B6 and have G2:H6 to sort along with it.
The cells in between in my master sheet have data so I can't rearrange the sheet.

[Marc L]

Hi,

unclear so according to your attachment join as well the expected result workbook …

[TheNewGuyFromPA]

If I sort by Customer name alphabetically then "AJ" in position B6 would move up to cell B4.
I need G6 and H6 to move to positions G4 and H4 respectively when that sort happens.

[Marc L]

And what happens to C:F cells ? As you can filter the global range A:H at once ! …

[TheNewGuyFromPA]

And what happens to C:F cells ? As you can filter the global range A:H at once ! …

This is a sample.
In my master file there is data in those cells that cannot be changed or sorted with the other cells.
The sort macro needs to ignore those cells.

[Marc L]

So just copy the 'key sort' column B to column I and apply the same sort on both ranges then delete column I …

[Kaper]

Of course one can write own sorting procedure.
For instance if we decide to go for bubble sort algorithm, a simple implementation could look like this:

Please Login or Register  to view this content.

Try it :-)

[rorya]

Depending on the nature of the actual file, you might also simply store the contents of the intervening cells in a variable, sort the entire block including the intervening cells, then write the old values/formulas back to the cells you didn't want sorted.

[TheNewGuyFromPA]

Depending on the nature of the actual file, you might also simply store the contents of the intervening cells in a variable, sort the entire block including the intervening cells, then write the old values/formulas back to the cells you didn't want sorted.

Sorry. I've been offline for a few weeks and didn't want to leave this topic hanging.
That's essentially what I ended up doing. I thought about inserting columns and moving the additional data to the adjacent cells in the search range then just expanding my original range in the macro.
But I used some empty cells as helpers and got the sorting to work like I want with VLOOKUP formulas.
So when the macro sorts the data, the VLOOKUP finds it in the same range that the macro uses and moves the cell contents to that corresponding row.

Thank you all for your help.
I'll mark the thread as solved.