Excel VBA Sort Separate Groups of Columns with same Sort Key - Seeking Better Efficiency

[cfrehner]

Hello,
I have a worksheet with data similar to the table below:

[TABLE="width: 500"]
<tbody>[TR]
[TD](Hidden)[/TD]
[TD]Sort[/TD]
[TD]Name[/TD]
[TD]Cost[/TD]
[TD](Empty for Spacing)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]AAA[/TD]
[TD]$20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]BBB[/TD]
[TD]$18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]CCC[/TD]
[TD]$25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[TD]DDD[/TD]
[TD]$15[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This group of 5 columns repeats up to 300 times across the worksheet. I currently have a macro that will sort these groups of columns depending on users' input. For example, the user may want these groups of columns sorted by 'Sort' number (which is how the data in the above table is sorted). Or the user may choose to sort the groups of columns by 'Cost' for example. I've named this selection 'SortKey' in the code below. The macro will then run through all the groups of columns using a loop like this code:

'Resizes selection to match the size of the data in the columns
Selection.Resize(numRows + 35, numColumns + 4).Select

'Sorts using the same Sort Key for each group of columns.  In our example, it would sort by 'Sort'
    Set SortRange = Selection
    SortRange.Sort Key1:=SortKey.Value, Order1:=xlAscending, Header:=xlGuess

'Moves to next group of columns
    ActiveCell.Offset(0, 5).Select

Here is my problem: this process is incredibly slow.

Here are my questions:
1. Is it possible to sort all the groups of columns at the same time - keeping them separately sorted - using the same Sort Key? In other words, Columns A:E sort by 'Sort', columns F:J sort by 'Sort', etc. but this is all completed at the same time? Would this improve the performance time of this macro?

2. Is there a more-efficient way to achieve these results? The data on this worksheet is pasted from another worksheet, and it is not possible for me to set up formulas on this spreadsheet to accomplish the same result.

Being that this is my first post, please let me know if I have posted anything incorrectly. Thank you in advance for your assistance!

 

[AlphaFrog]

Two suggestions:

1.) Add this at the start of the macro.
Aplication.ScreenUpdating = False

And this after all the sorts...
Aplication.ScreenUpdating = True


2.) Remove all the uses of .Select. They are time expensive and usually not necessary.

Aplication.ScreenUpdating = [COLOR=darkblue]False[/COLOR]

[COLOR=green]'Resizes selection to match the size of the data in the columns[/COLOR]
[COLOR=darkblue]Set[/COLOR] SortRange = Selection.Resize(numRows + 35, numColumns + 4)

[COLOR=darkblue]For[/COLOR] i = 0 [COLOR=darkblue]To[/COLOR] 300 [COLOR=darkblue]Step[/COLOR] 5 [COLOR=green]'Loop through 300 columns[/COLOR]
[COLOR=green]'Sorts using the same Sort Key for each group of columns.  In our example, it would sort by 'Sort'[/COLOR]
    SortRange.Offset(, i).Sort Key1:=SortKey.Value, Order1:=xlAscending, Header:=xlGuess
[COLOR=darkblue]Next[/COLOR] i

Aplication.ScreenUpdating = [COLOR=darkblue]True[/COLOR]

 

[cfrehner]

Thank you for the feedback. I should have mentioned that I have the ScreenUpdating set to False throughout the process. I'll try the removal of the Select statements to see if that helps.

Thanks again!

 

[cfrehner]

This definitely did speed up the process. The embarrassing part is that I knew this, just was blind to it for some reason...

Thanks again!!!!

 

[AlphaFrog]

You're welcome. Thanks for the feedback.