Limiting sort criteria

[knobblyknees]

Running Excel 2010.

Something changed when trying to sort. If I choose rows 1-50,000 and try to sort Column K then Column A, I get the warning about effecting a large number of cells and then get memory errors if I go ahead. It looks like it's picking up all the columns rather than recognizing the data only continues through Column AM. How can I force it back to recognizing the existing data set without highlighting the data set each time before sorting? Hiding the superfluous columns does not seem to fix the problem.

 

[SirJB7]

Hi, knobblyknees!


Try doing this:

a) activate related worksheet (click on tab for selecting)

a) enter to the VBA editor (Alt-F11)

b) go to the immediate window (Ctrl-G if not visible)

c) type this:

ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Clear

ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Add Key:=Range("K:K"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SetRange Range("A1:AM50000")

ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.Header = xlYes

ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.Apply

d) save the workbook, close it, quit Excel and reopen the file


Or copy the above code into a module (Alt-F11, Insert, Module) between two lines "Sub X" and "End Sub", and then run the macro (F5 from the VBA editor, Alt-F8 from the worksheet).


If that doesn't work, create a new worksheet, copy all data from older sheet and then delete it, set sort definitions again for new sheet, save the workbook.


Regards!

 

[knobblyknees]

I will try the code above, but that will only effect the one sheet. I had already tried copying and pasting value only data to a new sheet. It appears to be a global Excel problem. When I pull up the sort window on any of the test files the available sort fields extends past column IV (which is where it used to end) to column SW with an note that more columns are available. I am pretty sure this is the source of the problem, just not at all sure how to remedy it.

 

[SirJB7]

Hi, knobblyknees!


If the above code works, you can build a macro for running it on all sheets.

Just add these lines at top:

-----

Sub XXX()

Dim ws as Worksheet

For each ws in Activeworkbook.Worksheets

ws.activate

-----

And these at bottom:

-----

Next ws

End Sub

-----


Then run macro XXX and it will repeat all the stuff for all sheets in workbook.

I think you can handle If conditions if needed for selecting on which worksheets to do this or not.


BTW, there's a problem with the UsedRange property in Excel, which is is used with Ctrl-Shift-End among other things.

Give a look at this article, maybe you find it useful:

http://www.mrexcel.com/forum/showthread.php?t=548700


Regards!

 

[knobblyknees]

Forgot to send a big thank you. The script is great, but even better is the Ctrl-Alt-R utility you pointed me to. Now I've got back the ability to perform the simplest tasks in the simplest manner. Thanks again for your time and help!

 

[SirJB7]

Hi, knobblyknees!

Better later than never... Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!