Sorting A Range of Cells Horizontally - Defining Dynamic Sort Range

[Ark68]

I am using the line of code (in blue) below to attempt to sort a dynamic range of cells in row ("drow" starting at Z. Since the range is dynamic, I'm having difficulty figuring out how to define the range to be sorted.

Sub ExtractNamesToRow(grp As String, grp2 As String, drow As Long)
    Dim names() As String
    Dim j As Long, i As Long

    ' Split the string into an array using the comma as a delimiter
    names = Split(grp, ",")
    'drop the arra values horizontally from column Z
    ws_dump.Range("Z" & drow).Resize(1, UBound(names) + 1).Value = Application.Trim(names)
    'sort values right from Z
    ws_dump.Range(ws_dump.Cells(drow, 26), ????????).Sort Key1:=ws_dump.Range("Z" & drow), Order1:=xlAscending, Orientation:=xlLeftToRight
 
    i = 26
    Do While ws_dump.Cells(2, i) <> ""
        grp = grp & ", " & ws_dump.Cells(2, i)
        i = i + 1
    Loop
    i = 27
    Do While ws_dump.Cells(2, i) <> ""
        grp2 = grp2 & ", " & ws_dump.Cells(2, i)
        i = i + 1
    Loop
   
End Sub

An example,
Drow = 6
Z6 = "Zebra"
AA6 = "Alligator"
Need to sort Z6:AA6, to result in "Alligator" being in Z6 and "Zebra" in AA6

How best to adjust this code to accomplish the desired results?

 

[DanteAmor]

ws_dump.Range(ws_dump.Cells(drow, 26), ????????).Sort Key1:=ws_dump.Range("Z" & drow), Order1:=xlAscending, Orientation:=xlLeftToRight

Chage for this:

  Dim lc As Long
  lc = ws_dump.Cells(drow, Columns.Count).End(xlToRight).Column
  If lc < 26 Then lc = 26
  ws_dump.Range(ws_dump.Cells(drow, 26), ws_dump.Cells(drow, lc)).Sort Key1:=ws_dump.Range("Z" & drow), Order1:=xlAscending, Orientation:=xlLeftToRight

 

[Ark68]

Thank you @DanteAmor, this works. Should I be getting a value of 16384 for lc if I only have values in Z-AA? I was hoping lc would give me the number of cells occupied. I do eventually need to know how many cells are occupied after, and including, Z.

 

[Peter_SSs]

That should be

  lc = ws_dump.Cells(drow, Columns.Count).End(xlToLeft).Column

 

[DanteAmor]

Peter's assessment is correct, it should be xltoleft.

Or 1, I'm used to using the number I forget the syntax for the text:

  Dim lc As Long
  lc = ws_dump.Cells(drow, Columns.Count).End(1).Column
  If lc < 26 Then lc = 26
  ws_dump.Range(ws_dump.Cells(drow, 26), ws_dump.Cells(drow, lc)).Sort Key1:=ws_dump.Range("Z" & drow), Order1:=xlAscending, Orientation:=xlLeftToRight

You can use the texts as parameters or numbers, look at the following image:

You can use:
xlToLeft = 1
xlToRight = 2
xlUp = 3
xlDown = 4

 

[Ark68]

Thank you for the lesson!