VBA undo sorted records

Question

Can anyone please help me on the below scenario

I am able to sort the records using VBA on a particular cloumn ("M1") , now how to undo and keep the records in original position

Sub Sor_t()

  Dim oneRange As range 

  Dim aCell As range   

  Set oneRange = ThisWorkbook.Sheets("RMData").UsedRange   

  Set aCell = range("M1")   

  oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlYes

End Sub

Thanks in advance Ram

Can you try `Sheets("RMData").ShowAllData` – Tehscript Apr 12 '18 at 12:17 — Tehscript, Apr 12 '18 at 12:17
copy paste the original data beforehand, then sort it? – dv3 Apr 12 '18 at 12:21 — dv3, Apr 12 '18 at 12:21

score 0 · Answer 1 · answered Apr 12 '18 at 12:21

you can copy the records to another column and then sort, so you keep the records on the original position; or,
you can asign a position number to your current records from 1 to n and then sort that column when you want to return to original...

score 0 · Accepted Answer · answered Apr 12 '18 at 12:22

VBA clears the Undo buffer (can't undo the sorting)

However you can accomplish this with a workaround

With the records unsorted create a new column (hidden), with sequential numbers
Sort the records on any column, then revert to the initial sort using the hidden column

--- Unsorted B column:

  Col A    Col B
    1        K
    2        Z
    3        A

--- Sorted B column:

  Col A    Col B
    3        A
    1        K
    2        Z

--- now sort by column A again

VBA undo sorted records

2 Answers2