05-23-2020 02:56 PM
05-23-2020 06:21 PM
Is it possible to explain the exact procedure to perform to differentiate the unsorted cases and sorted cases?
For example, do you need to delete some rows or columns?
Also, are you sorting column or row wise? Somehow difficult to understand what your intention is.
05-24-2020 12:36 AM
Sub SortAllRows() Dim LRow As Long Dim i As Long Dim MyRow As Range Dim MyKey As Range With ThisWorkbook.Worksheets("Sheet1") LRow = .Cells(.Rows.Count, 1).End(xlUp).Row For i = 1 To LRow Set MyRow = .Range("B1:I1").Offset(i - 1, 0) Set MyKey = Application.Index(MyRow, 1) MyRow.Sort Key1:=MyKey, Order1:=xlAscending, Orientation:=xlLeftToRight Next i End With End Sub
05-24-2020 02:14 AM
05-24-2020 02:24 AM
05-24-2020 02:56 AM - edited 05-24-2020 04:19 AM
Yes, it's VBA. Of course it can be incorporated within an existing procedure that you have, though I'd suggest testing it as a standalone macro first, to check it's giving you the desired results. Currently it is set up to work on ranges beginning with B1:I1 and successive rows, just as per your screenshot. Obviously amend as required.
05-24-2020 01:49 PM
05-24-2020 02:44 PM - edited 05-24-2020 02:46 PM
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).
05-26-2020 04:04 AM - edited 05-26-2020 04:05 AM
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.
05-26-2020 04:46 AM
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 Sub
05-26-2020 10:59 AM
by calof1 on June 23, 2019