sort rows by pairs of columns

Hello everyone,

I am trying to record a macro that will sort my data by rows and keep columns in pairs of 2.

for example,

unit1 unit2 unit3
quantity price Quantity price Quantity price
100 30 200 28 500 22
100 39 200 26 500 2
100 333 200 26 500 255
100 29 200 26 500 33

And i want to sort every row based on the price of each pair. transform it to this:

unit1 unit2 unit3
quantity price Quantity price Quantity price
500 22 200 28 100 30
500 2 200 26 100 39
200 26 500 255 100 333
200 26 100 29 500 33

my code and sample workbook are attached but here is also what i have accomplished so far.
My code works for sorting rows but it doesn't give me the pairing sorting i want.

Please Login or Register  to view this content.

Thanks in advance.

Any help is more than welcome

Administrative Note:

Greetings from London.

The good news is what you ask for is quite simple.

The Bad News is 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. No help to be offered until this moderation request has been fulfilled.)

Thanks.

Try this code:

Please Login or Register  to view this content.

This is shorter and probably faster:

Please Login or Register  to view this content.

I would like to thank you again for your prompt response to my issue.

At first glance, it looked fine and working perfectly.

I would like to point a few issues that i discovered in the process.

Firstly, if i run the code and then change some values and try to execute the code again it doesn't sort the values at all.

Secondly, it doesn't look to sort every value i choose to input.

For example if i apply the code to this dataset:

Attachment 697267

the outcome i get is this:

Attachment 697268

It seems it doesn't sort all the rows correctly. I would also like to have the option to add more pairs of columns to the dataset.

The sample workbook is attached.

Thank you in advance for any further help you may provide!

Here is the code . Even if duplicates are there code works.

Please Login or Register  to view this content.

Hi

I misunderstood your requirement.

I was sorting by the first column of the pair. You need it sorted by the second column of the pair.

Please Login or Register  to view this content.

here is the new code. To change the columns change this line in code suitably.
Line

Please Login or Register  to view this content.

Code for macro

Please Login or Register  to view this content.

To avoid decimal problem in the code change this line

Please Login or Register  to view this content.

as

Please Login or Register  to view this content.

and this line

Please Login or Register  to view this content.

as

Please Login or Register  to view this content.

Please follow kvsrinivasamurthy's advice.

my code works for decimals in the price column.