Excel vba loop through range alphabetically

Question

I want to loop through a range of cells alphabetically to create a report in alphabetical order. I dont want to sort the sheet as the original order is important.

Sub AlphaLoop()

'This is showing N and Z in uppercase, why?
For Each FirstLetter In Array(a, b, c, d, e, f, g, h, i, j, k, l, m, N, o, p, q, r, s, t, u, v, w, x, y, Z)
    For Each SecondLetter In Array(a, b, c, d, e, f, g, h, i, j, k, l, m, N, o, p, q, r, s, t, u, v, w, x, y, Z)
        For Each tCell In Range("I5:I" & Range("I20000").End(xlUp).Row)
            If Left(tCell, 2) = FirstLetter & SecondLetter Then
                'Do the report items here
        End If
        Next
    Next
Next

End Sub

Note that this code is untested, only sorts by the first 2 letters and is time consuming as it has to loop through the text 676 times. Is there a better way than this?

Answer 1

Try approaching from a different angle.

Copy the range to a new workbook

Sort the copied range using Excels sort function

Copy the sorted range to an array

Close the temp workbook without saving

Loop the array using the Find function to locate the value in order and run your code.

Post back if you need help writing this but it should be fairly simple. You will need to transpose the range to the array and you will need to dim your array as a variant.

This way you only have one loop, using the nested loops blows them out exponentially