Sort code without using .sort in my VBA code

[JackDanIce]

Hi all,

Had a question put to me recently so wanted to see what other people on the board's approach would be.

I have some data with a header row, let's say 5 rows and 5 columns. How can I sort this data using VBA code only but without using the .sort word in my code? For argument's sake let's say data is in range A1:E5 and header is B1. I'm not fussed if a quick sort or bubble sort method is used, just the most efficient.

Approach I came up with is:

Sub Sort_Marco()
Dim i As Long, j As Long

j = Range("A" & Rows.Count).End(xlUp).Row + 1
i = 2
Do
    If Range("B" & i) > Range("B" & i + 1) Then
        Range("B" & i).EntireRow.Cut Range("A" & j)
        Range("B" & i).EntireRow.Delete shift:=xlUp
        i = 2
    End If
i = i + 1
Loop While Not IsEmpty(Range("B" & i + 1))

End Sub

Above loop would be cumbersome if there is more than 5 rows of data as each time a value is found out of order, I reset it to start at the beginning to scan down until a new out of place value is found. I'm guessing a nested loop would be faster but I'm after finding either the shortest amount of code to do this or the fastest completion code (i.e. when there's lots of rows)

Any thoughts/comments please?

Thanks,
Jack

PS Excuse my lack of response as I'm going away for the weekend in 20mins and won't be checking emails until I return. The above is just for my own knowledge to see what people post. Cheers!

 

[xld]

Drop the data into an arra7y and use a QuickSort.

But I would use Sort, dumb not to.

 

[JackDanIce]

Thanks xld, would you mind writing a bit of code to show me that? I assume you'd need to initialise a 2D array to match or be bigger than the size of the data?

I agree with you, stupid not to use .Sort but that's what I was asked so...

 

[xld]

No, you load it directly

Dim rng As Range
Dim myArray As Variant

Set rng = Range("A1").Resize(Cells(Rows.Count, "A").End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column)
myArray = rng

 

[JackDanIce]

Thanks, will play with that