CODEWITHSUNDEEP
excelvba
Swiftslide
Swiftslide

Reputation: 1347

Excel VBA: Sort a worksheet virtually?

I have a worksheet with a bunch of rows in no particular order. To speed up my macro I want to sort the entries alphabetically by "Name" (column B). However, my boss doesn't want the sheet actually sorted - god knows why, but he's the boss.

Basically what I would like to do is sort the range, run my macro and then return it to the original order. But because there is no "order" to the original order, I can't simply use a different sorting method.

I know about the quick sort algorithm and I could always split the range into an array of ranges, one entry for each row, then sort that array. But I was wondering if there's a more efficient method?

Upvotes: 0

Views: 758

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149297

Further to my comment, here is an example which keeps your original sheet untouched.

Sub Sample()
    Dim ws As Worksheet

    Set ws = Sheets("Sheet1").Copy(Before:=Sheets(1))

    With ws
        .Columns("B:B").Sort key1:=.Range("B1"), order1:=xlAscending

        '
        '~~> Do your stuff
        '
    End With

    '~~> Delete sheet once you are done.       
    Application.DisplayAlerts = False
    ws.Delete
    Application.DisplayAlerts = True

    Set ws = Nothing
End Sub

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53137

You can add another column to your sheet (permanently or temporarily), that holds a sort index number (numbers sequentially down the sheet). Once you have done what you need to do, sorting on this extra column will put the data back as it was.

Upvotes: 1

Related Questions