Sorting rows and columns with VBA

[DanzaNZ]

Hi there

I have an issue with sorting rows / cols...
My aim is to sort columns from smallest to largest but keep this by row.

Example:
Row before change: 27 | 32 | 01 | 34
Row after change : 01 | 27 | 32 | 34


Here is my code so far however the it requires a loop which is not really the best option.

Please Login or Register  to view this content.

Any help would be really great

Thanks
D

[Leith Ross]

Hello DanzaNZ,

You can sort the rows (horizontal data) by declaring the entire range as an object rather than selecting each of the rows individually. In VBA it is rare that you need to select anything before an operation is performed. Selecting also increases the macros execution time.

If you can post your workbook that would best. It will allow solutions to be tested directly for correctness.

[DanzaNZ]

Hi Leith Ross

Thanks for the reply.
A sample is attached

[Leith Ross]

Hello DanaNZ,

I was wrong. In Excel 2007 through 2016 the maximum number of sort fields in a single sort operation is 64. The number of sorts is unlimited if you only use one sort field at a time in a loop.

The odd thing was even my code would sort only one row at a time using VBA. However, selecting the range in Excel and using the context menu to sort would sort all the rows correctly.

I have been looking for any other reports of this problem. So far, I found no references to it but will keep looking.

[DanzaNZ]

Hi Leith Ross

Thank you for looking into this.
I've searched high and low for something suitable however the loop looks to be the only thing that works.
At the moment I use a vbs code to run the macro without opening the workbook, which helps with the screen updating rubbish.
Maybe there is a away to sort the rows using vbs without macros from the workbook. How is your knowledge with vbs? Mine is pretty basic.

[Leith Ross]

Hello DanaNZ,

It seems you are correct that using a loop is the only with the rows. I did create a loop that works well for sorting the rows. A button has been added to the sheet to run it.

In the attached workbook, your code has been moved to Module1 as not to interfere with macro called by the buttons. Here is the macro code. Try this and let me know if this will work for your needs.

Sort By Rows Macro

Please Login or Register  to view this content.

[DanzaNZ]

Great!

Works really well, thank you for your help.

[Leith Ross]

Hello DanaNZ,

You're welcome. I learned something new today.