Loop through each row in range to sort row values alphabetically

[ilsley_excel]

Hi. I have the following routine that I am trying to manipulate in order to loop through each row in a dynamic range and sort the values alphabetically (i.e. a horizontal sort).

Sub SortRowRange(Target As Range, Optional Desc As Boolean)
    Dim r As Range
    Dim list As Object
    Set list = CreateObject("System.Collections.ArrayList")
    For Each r In Target
        list.Add r.Value
    Next

    list.Sort

    If Desc Then list.Reverse

    For Each r In Target
        r.Value = list(0)
        list.Remove list(0)
    Next

End Sub

'Call SortRowRange to go through each row in a range

Sub AlphabetiseRows()

 Dim lastRow As Long
 Dim DataRange As Range
 
 lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
 Set DataRange = ActiveSheet.Range("I" & lastRow & ":W" & lastRow)
 
 For Each Row In DataRange
  
   'Start at row 2
    Rows.Value = 2
    SortRowRange Range(DataRange)
 
 Next

End Sub

... but I keep getting a 'method Range of object_ Global failed' error.

Any ideas?

Thanks.

 

[Peter_SSs]

Couldn't you just use Excel's built-in sort facility.? Something like this?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sub AlphabetiseRows_v2()
  Dim Rw As Range
  Dim lastRow As Long
 
  lastRow = Range("A" & Rows.Count).End(xlUp).Row
  Application.ScreenUpdating = False
  For Each Rw In Range("I2:W" & lastRow).Rows
    Rw.Sort Key1:=Rw, Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
  Next Rw
  Application.ScreenUpdating = True
End Sub

 

[ilsley_excel]

Sub AlphabetiseRows_v2() Dim Rw As Range Dim lastRow As Long lastRow = Range("A" & Rows.Count).End(xlUp).Row Application.ScreenUpdating = False For Each Rw In Range("I2:W" & lastRow).Rows Rw.Sort Key1:=Rw, Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight Next Rw Application.ScreenUpdating = True End Sub

Thank you so much! That worked!

Also - my profile shows the correct version. I'm still using Excel 2010!

 

[Peter_SSs]

Thank you so much! That worked!

You're welcome. Thanks for the confirmation.

Also - my profile shows the correct version. I'm still using Excel 2010!

Your profile does not show any version(s)

If your version(s) were in your profile, it would show like this

 

[ilsley_excel]

You're welcome. Thanks for the confirmation.


Your profile does not show any version(s)
View attachment 92289
If your version(s) were in your profile, it would show like this

View attachment 92290

I have done this now. Thanks!

 

[Peter_SSs]

I have done this now. Thanks!

Great! Thanks for doing that.