Forum Discussion

Odintsov_Maksim's avatar
Odintsov_Maksim
Copper Contributor
May 23, 2020
Solved

Large amount of data sorting

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.
BI & Data Analysis
Macros and VBA
  • Jos_Woolley's avatar
    Jos_Woolley
    May 26, 2020

    Odintsov_Maksim 

     

    Apologies. You're correct. Small adjustment needed: change

     

    If MyCol <> 1 Then

    to

    If MyCol > 2 Then

     

    Regards

     

     

Jos_Woolley's avatar
Jos_Woolley
Iron Contributor
May 24, 2020

Odintsov_Maksim 

 

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

  • Odintsov_Maksim's avatar
    Odintsov_Maksim
    Copper Contributor
    May 24, 2020
    Is this a VB code? Will it work like a macro, can I include it in an existing macro in edit mode?
    Will this sort the cases in each row as I described?
    • Jos_Woolley's avatar
      Jos_Woolley
      Iron Contributor
      May 24, 2020

      Odintsov_Maksim 

       

      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

       

      • Odintsov_Maksim's avatar
        Odintsov_Maksim
        Copper Contributor
        May 24, 2020
        Yes, thanks, the code works. But 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. 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.

Resources