VBA Excel macro works in one worksheet, but not different worksheets, nor new spreadsheets

Question

I wrote a VBA macro in Excel to sort a row of numbers horizontally in ascending order, and then move onto the next row and repeat the process until it hits empty cells / non-numeric data, as this isn't standard functionality for Excel.

The data is 73 rows of 7 columns, numbers not exceeding 50.

The code works in one spreadsheet file, on a worksheet called "Sheet1", however, if the Range dimensions are pointed at any other worksheet, the macro does not work in them.

Sub SortAndMove()
    Dim rng As Range
    
    ' Set the initial range to the second row, columns A to F
    Set rng = Worksheets("Sheet1").Range("A1:G1")
    
    ' Loop until the first cell in the current row is not a number or is <= 0
    Do While IsNumeric(rng.Cells(1, 1).Value) And rng.Cells(1, 1).Value > 0
        ' Sort the selected range horizontally in ascending order
        rng.Sort Key1:=rng.Cells(1, 1), Order1:=xlAscending, Header:=xlNo
        
        ' Move the selection down one row
        Set rng = rng.Offset(1, 0).Resize(, 7) ' Resize to select the first six columns

    Loop
End Sub

The new code is essentially the same, apart from a different subroutine name and the addition of a counter:

Sub HorizontalSort()

    Dim rng As Range
    Dim counter As Range
    Dim i As Integer
    
    
    ' Set the initial range to the second row, columns A to G
    Set rng = Worksheets("Sheet1").Range("A1:G1")
    
    ' Sets the range where the counter will be updated on the worksheet
    Set counter = Worksheets("Sheet1").Range("I1")
    i = 0
    
    
    ' Loop until the first cell in the current row is not a number or is <= 0
    Do While IsNumeric(rng.Cells(1, 1).Value) And rng.Cells(1, 1).Value > 0
        ' Sort the selected range horizontally in ascending order
        rng.Sort Key1:=rng.Cells(1, 1), Order1:=xlAscending, Header:=xlNo
        
        ' Move the selection down one row
        Set rng = rng.Offset(1, 0).Resize(, 7) ' Resize to select the first seven columns
        
        ' Increment the counter and print it's value during each iteration of the loop
        i = i + 1
        counter.Value = i
        
    Loop
    
End Sub

I have:

• Obviously renamed those Range references to different sheets, for example "hsorter" for a worksheet called hsorter. The block of code will only work when it references "Sheet1" in the worksheet Sheet1.
• I wrote the second block of code in a completely new spreadsheet file, and it does not function in any worksheet, no matter what it is named.
• Have also tried using ActiveSheet instead of declaring the specific sheet.
• Included a counter in the second block of code, to ensure the script was running through every line, and it is.
• Tried changing the formatting of the cells in the new spreadsheet to general, number and custom ("00") to no avail.

Can anyone see anything wrong with my code, or think of a reason why this would not work?

I can work around but sorting my data in the sheet that works, but that's annoying and beside the point.

Answer 1

Sort Rows

Example (Test)

Sub HorizontalSort()
    ' Set the workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' Set the worksheet.
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    ' Set the range (`A1:GlastRow`).
    Dim rg As Range: Set rg = RefRangeFind(ws, "A1:G1")
    ' Sort each row of the range ascending.
    SortRowsAsc rg
End Sub

Help: Reference

Function RefRangeFind(ByVal ws As Worksheet, ByVal FirstRow As String) As Range
    With ws.Range(FirstRow)
        Dim lcell As Range: Set lcell = .Resize(ws.Rows.Count - .Row + 1) _
            .Find("*", , xlValues, , xlByRows, xlPrevious)
        If lcell Is Nothing Then Exit Function ' only blanks
        Set RefRangeFind = .Resize(lcell.Row - .Row + 1)
    End With
End Function

Help: Sort

Sub SortRowsAsc(ByVal rg As Range)
    If rg Is Nothing Then Exit Sub
    Dim rrg As Range
    For Each rrg In rg.Rows
        rrg.Sort rrg, xlAscending, , , , , , xlNo, , , xlSortRows
    Next rrg
End Sub