May 23 2020 02:56 PM
Please, help.
Need to sort numeric data contained in large numbers (several thousand) strings.
The data in each string must be sorted separately.
How can this be done quickly.
May 23 2020 03:10 PM
it might be difficult to visualize what you are trying to achieve without showing a sample data. Are you able to show a sample data after removing sensitive information?
Cheers
May 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.
Cheers
May 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
Regards
May 24 2020 02:14 AM
May 24 2020 02:24 AM
May 24 2020 02:56 AM - edited May 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.
Regards
May 24 2020 01:49 PM
May 24 2020 02:44 PM - edited May 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).
Regards
May 26 2020 04:04 AM - edited May 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.
May 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
Regards
May 26 2020 10:59 AM
May 26 2020 11:56 AM
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
May 26 2020 12:34 PM
@Jos_Woolley File attached
May 26 2020 01:01 PM
Solution
Apologies. You're correct. Small adjustment needed: change
If MyCol <> 1 Then
to
If MyCol > 2 Then
Regards
May 26 2020 01:18 PM
May 26 2020 01:01 PM
Solution
Apologies. You're correct. Small adjustment needed: change
If MyCol <> 1 Then
to
If MyCol > 2 Then
Regards