How to Unsort Sorted Data in Microsoft Excel VBA

1. How to Unsort Sorted Data in VBA
2. Method 1: Keep a Temporary Column With Increasing Numeric Values
3. Method 2: Use the MS Excel Tools
4. Method 3: Keep a Backup of the Data
5. Conclusion

VBA is a programming language for Microsoft Excel applications that provides users with various functions and features. This article will teach us ways to unsort data in VBA.

How to Unsort Sorted Data in VBA

Sorting is a very common practice when working with MS Excel. One may want to sort data in ascending or descending order to work out the highest or lowest of some values, organize the data, etc.

Sorting in Excel can be done by either:

1. Using MS Excel sorting and filtering options
2. Using VBA Range.Sort method

However, one might perform these actions and later realize that they must revert the data to its original form. There is the standard Ctrl+Z undo option available, but there are situations when this does not work.

For example:

1. If you have saved the Excel file and reopened it.
2. If you have made some additional changes and do not want to revert those, rather you want to get rid of the sorting only.
3. If you have done the sorting using the VBA code.

Therefore, there must be some proper method to work around this limitation, to revert to the original data organization in your Excel sheet. Let us look at those methods one by one.

Method 1: Keep a Temporary Column With Increasing Numeric Values

This method allows us to unsort data by keeping a reference to the original order of the data. Let us understand this using an example.

Suppose you have the following data in your sheet:

Now, to preserve the original order of data, we can insert a temporary column that keeps track of it. This means that we will save the serial number values in a column.

This can be done by following these two easy steps:

1. In the first empty column, fill the first two cells with the values 1 and 2.

   

2. Select these two cells and drag them down from the bottom right corner, where a plus sign will appear. This will fill the whole column with incrementing numeric values.

   

Alternatively, you can create this temporary column by using the following code in VBA:

Sub CreateColumn()

For m = 2 To Cells(Rows.Count, "A").End(xlUp).Row
If Cells(m, "A").Value <> "" Then
Cells(m, "C").Value = m - 1
End If
Next m

End Sub

Note: You will have to change the value of m and the column names according to the data in your sheet.

Now that you have the temporary column, you can sort data all you want, and you will always be able to bring it back to this state. For example, let us run the following Macro to sort the data according to salary:

Sub Sort()

Set sortRange = Range("A1:C6")
Set keyCell = Range("B1")
sortRange.Sort Key1:=keyCell, Order1:=xlAscending, Header:=xlYes

End Sub

The data is now sorted like this:

If you want to unsort the data, just run the sorting code again, but this time keeps Key1 as the temporary column so the values are sorted according to it. We will do this as follows:

Sub Sort()

Set sortRange = Range("A1:C6")
Set keyCell = Range("C1")
sortRange.Sort Key1:=keyCell, Order1:=xlAscending, Header:=xlYes

End Sub

The data is now unsorted as it was originally.

Note: You can also sort in the example above using MS Excel Sort & Filter options in the Home menu instead of the VBA code.

Method 2: Use the MS Excel Tools

MS Excel provides some sorting and filtering tools with various options. You can specify which column you want to sort on and the order.

For example, if we want to sort the original data according to salaries, we can select the following in the Custom Sort sorting option.

In most versions of MS Excel, there is an option to clear all the sorting and filtering. The option is available through HOME > EDITING > SORT & FILTER > CLEAR.

Method 3: Keep a Backup of the Data

One way to unsort data is to keep a backup copy of your Excel files. This is extremely useful in cases of accidental misuse.

When you want to unsort, you can refer to the backup file and use the raw data saved there in the original order. Here are a few methods to create a backup file:

1. Copy and paste the data into a separate sheet in the workbook.
2. Create a copy of the whole Excel file.
3. Automate the process of creating a backup with a VBA timestamp Macro which creates a copy at a specific time each day. This way, you will only have to manually create a backup occasionally.
4. Create a backup copy in the same location as the original file using VBA.
5. Create a backup copy in another location using VBA. Saving in another location helps with accidental deletion or erasure of the data.

Note: You can use the Save and SaveAs methods accordingly to create a backup copy using VBA.

Conclusion

This sums up our discussion on different methods to unsort data in VBA, a useful programming language that saves valuable time for Microsoft Office users. The methods we discussed are:

1. Keeping a temporary column with incrementing numeric values
2. Using the MS Excel tools
3. Keeping a backup of the data