Forum Discussion
Large amount of data sorting
Apologies. You're correct. Small adjustment needed: change
If MyCol <> 1 Thento
If MyCol > 2 ThenRegards
It would be better to establish control of macro completion by column "A", in which there are always values. How to do it?
And further. Perhaps the macro could be improved if there is't specify a specific sorting range, but the current row as a whole. And is it possible not to specify the sheet name so that the macro runs on any Excel sheet?
I am not good in VBA syntax, sorry.
Re "the process stops when only empty cells are present in the sorting range. It would be better to establish control of macro completion by column "A", in which there are always values.", I'm confused: it is precisely column A which controls completion of the macro: the code I supplied will process all rows from row 1 up to the row in the worksheet which contains the last non-blank entry in column A. As such, I'm not sure what you mean: did you change anything in the code I provided?
Re "Perhaps the macro could be improved if there is't specify a specific sorting range, but the current row as a whole." I would never code a macro to process more cells than are necessary, and certainly not an entire row's worth (16384 cells). If the last column to be processed is subject to change, this can be detected as part of the macro, which is far better than blanket-setting it to the very end of the worksheet.
Re "And is it possible not to specify the sheet name so that the macro runs on any Excel sheet?" Absolutely. If you could clarify the confusion re my first point above with respect to your not thinking the process is controlled by the entries in column A then I'll happily post a revised version of the code.
Perhaps you should consider posting an actual workbook (with dummy data as opposed to real data, if necessary).
Regards
- OK, worked.
Will be testing.
Thank you so much! Apologies. You're correct. Small adjustment needed: change
If MyCol <> 1 Thento
If MyCol > 2 ThenRegards
Jos_Woolley File attached
Column A should not be affected whatsoever by the macro I posted. Can you post a workbook with an example in which column A is affected?
Regards
- Well thank you.
It works.
Only one problem.
Column "A" contains the numbering of records. And column “A” should not be involved in the sorting procedure. The data from column "A" should always remain in the same place.
Regards, thanks Not entirely sure what you mean there, sorry.
The modified version below detects the last non-empty cell in each row and applies the sort on a range with that cell as the upper bound. Note that, if any of the blank cells in your range in fact contain the null string (""), e.g. as a result of formulas within those cells, then it will not give correct results - in that case let me know and I will modify the code.
I have also amended it so that it runs on whichever is the active sheet at the time of running.
Sub SortAllRows() Dim LRow As Long Dim i As Long Dim MyRow As Range Dim MyCol As Long Dim MyKey As Range With ActiveSheet LRow = .Cells(.Rows.Count, 1).End(xlUp).Row For i = 3 To LRow MyCol = .Cells(i, .Columns.Count).End(xlToLeft).Column If MyCol <> 1 Then Set MyRow = .Cells(i, 2).Resize(, MyCol - 1) Set MyKey = Application.Index(MyRow, 1) MyRow.Sort Key1:=MyKey, Order1:=xlAscending, Orientation:=xlLeftToRight End If Next i End With End SubRegards
About the first point. Sorry, I made a mistake. Everything works.
Regarding the second point. I did not mean expanding the diapason of cells to the maximum size, but using a function such as selecting a row (shift + space from the keyboard) and sorting with a heading from column "A". Maybe I'm wrong, and in macros it's better not to do that.
Example attached below.