MS VBA to reorder columns in Excel

Recently, I encountered a challenge when using SharePoint surveys . It came to my attention that SharePoint surveys preserve the original order in which they were created, ensuring the integrity of the question sequence. As a result, even if the order of questions is modified within SharePoint, the downloaded survey results will still maintain the original order when exported to Excel. This problem can be solved by utilizing scripting in SharePoint, however, due to the security concerns scripting might not be available.

It turns out that it is possible to address this issue without using SharePoint scripting capabilities by reordering the columns in Excel. In this article, we will discuss how to implement column reordering in VBA , along with how to generate random data and reorder arrays.

FISHER-YATES SHUFFLE ALGORITHM

The Fisher-Yates shuffle algorithm is an efficient method for randomly shuffling the elements of an array that ensures that each element in the array has an equal probability of being placed in any position. The algorithm works by iterating over the array from the last element to the first. At each iteration, it randomly selects an element from the remaining unshuffled portion of the array and swaps it with the current element. This process continues until all elements have been iterated over, resulting in a fully shuffled array. We will utilize this algorithm in the next section to randomly shuffle a sequence of numbers ranging from 1 to 100. The resulting reshuffled sequence will serve as the desired order of columns in Excel in the following section.

A?variant ?type used below is a special kind of data type that can contain different kinds of information (think of it as a list). Ubound() and LBound() functions return a?Long ?containing the largest/smallest available subscript for the indicated dimension of an?array (by default, the dimension is 1). The Step parameter determines the increment or decrement by which the counter changes at each iteration of the loop.

Sub ShuffleArrayFisherYates(arr() As Variant)

? ? Dim i As Long
? ? Dim j As Long
? ? Dim temp As Variant
??
? ? ' Initialize the random number generator
? ? Randomize
? ??
? ? ' Shuffle the array using the Fisher-Yates algorithm
? ? For i = UBound(arr) To 2 Step -1
? ? ? ? j = Int(i * Rnd + 1)
? ? ? ? temp = arr(i)
? ? ? ? arr(i) = arr(j)
? ? ? ? arr(j) = temp
? ? Next i?

End Sub        

RANDOM DATA GENERATION

Let's populate Excel's "Sheet1" with the random data of 4 types: random words from a list of twenty colours, random numbers between 1 and 10000, random 5-digit zip codes, and random street names from a list of twenty Toronto's street names. First row of generated data will contain the desired order of columns.

Random number generation in VBA is performed via the Rnd function. To produce random integers in a given range the following formula should be utilized:

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)        

For example, to generate a random integer between 1 and 10:

Sub RandomInteger()
Dim MyValue As Integer
MyValue = Int((10 * Rnd) + 1)  ' Generate random value between 1 and 10
Debug.Print MyValue 
End Sub        

The code below performs a random reshuffle of a sequence of numbers from 1 to 100 (using ShuffleArrayFisherYates() Sub procedure defined above), writes the resulting sequence into the first row of "Sheet1" and populates each of 100 columns (rows 2:101) with one of four types of random data types described above.

Sub PopulateRandomData()
? ??
? ? Dim ws As Worksheet
? ? Set ws = ThisWorkbook.Sheets("Sheet1") ' change "Sheet1" to your sheet name
? ? Dim sequence(1 To 100) As Variant
? ? Dim sequenceString As String
? ? Dim i As Long
? ? Dim randomWords() As Variant
?   Dim streetNames() As Variant

? ? randomWords = Array("Red", "Blue", "Green", "Yellow", "Orange", "Purple", "Pink", "Brown", "Black", "White", _
? ? ? ? ? ? ? ? ? ? ? ? "Gray", "Cyan", "Magenta", "Lime", "Teal", "Indigo", "Maroon", "Navy", "Olive", "Silver")
? ??
? ? streetNames = Array("Yonge Street", "Queen Street West", "Bloor Street", "Spadina Avenue", "King Street West", _
? ? ? ? ? ? ? ? ? ? ? ? "Bay Street", "Dundas Street West", "College Street", "Front Street West", "Adelaide Street West", _
? ? ? ? ? ? ? ? ? ? ? ? "Gerrard Street East", "Danforth Avenue", "St. Clair Avenue West", "Eglinton Avenue West", _
? ? ? ? ? ? ? ? ? ? ? ? "Queen Street East", "Kingston Road", "Jane Street", "Dufferin Street", "Lakeshore Boulevard West", _
? ? ? ? ? ? ? ? ? ? ? ? "Steeles Avenue West")
? ??
? ? Dim rowNum As Long
? ? Dim colNum As Long
? ? Dim numColumns As Long
? ? Dim UBRW, LBRW, UBST, LBST As Long
? ? numColumns = 100
? ??
? ? For i = 1 To 100
? ? ? ? sequence(i) = i              ' a sequence from 1 to 100
? ? Next i
? ??
? ? ShuffleArrayFisherYates sequence ' shuffle in random order
? ??
? ? ' Clear existing data in the range
? ? ws.Range("A1:CV1001").ClearContents
? ??
? ? Range("A1:CV1") = sequence? ? ? ?' populate first row using the reshuffled sequence
? ??
? ? UBRW = UBound(randomWords)
? ? LBRW = LBound(randomWords)
? ? UBST = UBound(streetNames)
? ? LBST = LBound(streetNames)
? ??
? ? ' Populate data for the remaining rows
? ? For rowNum = 2 To 1001
? ? ? ? For colNum = 1 To numColumns
? ? ? ? ? ? Select Case colNum Mod 4
? ? ? ? ? ? ? ? Case 0 ' Random words
? ? ? ? ? ? ? ? ? ? ws.Cells(rowNum, colNum).Value = randomWords(Int((UBRW - LBRW + 1) * Rnd + LBRW))
? ? ? ? ? ? ? ? Case 1 ' Random numbers between 1 and 10000
? ? ? ? ? ? ? ? ? ? ws.Cells(rowNum, colNum).Value = Int(10000 * Rnd + 1)
? ? ? ? ? ? ? ? Case 2 ' Zip codes using 5 digits
? ? ? ? ? ? ? ? ? ? ws.Cells(rowNum, colNum).Value = Int((99999 - 10000 + 1) * Rnd + 10000)
? ? ? ? ? ? ? ? Case 3 ' Street names
? ? ? ? ? ? ? ? ? ? ws.Cells(rowNum, colNum).Value = streetNames(Int((UBST - LBST + 1) * Rnd + LBST))
? ? ? ? ? ? End Select
? ? ? ? Next colNum
? ? Next rowNum
? ??
? ? MsgBox "Random data populated successfully!"
? ??
End Sub        

By running PopulateRandomData() Sub procedure, data similar to the following will appear in "Sheet1" of Excel (please note that data will be different each time the procedure is called due to the random nature of variables generated):

First row contains the desired order of columns. For example, Column "U" should be first and Column "A" should be 70th.

COLUMN REODERING

The following Sub procedure will perform reordering of columns according to the order specified in row 1 of "Sheet1" ( by slightly modifying the code below you can utilize it for reordering of your data). Algorithm looks at the first raw of data and loops over numbers from 1 to 100 matching each number with the cell that contains the number recording the column number of the cell in the process (for example, column "U" will be registered as first column because in cell (1,21) there is 1 and so on).

Sub ReorderColumns()


? ? Dim originalSheet As Worksheet
? ? Set originalSheet = ThisWorkbook.Sheets("Sheet1") ' replace "Sheet1" with your sheet name
? ??
? ? Dim targetSheet As Worksheet
? ? Set targetSheet = ThisWorkbook.Sheets.Add(After:=originalSheet)
? ? targetSheet.Name = "Reordered Columns"
? ??
? ? Dim numbersRange As Range
? ? Dim columnOrder As Variant ' correct column order
? ? Dim originalColumn As Long
? ? Dim targetColumn As Long
? ? Dim columnNumbers(1 To 100) As Variant
? ??
? ? ' Set the range to search for numbers (modify as needed)
? ? Set numbersRange = originalSheet.Range("A1:CV1")?' replace with your range
? ??
? ? ' Loop through each number from 1 to 100
? ? For i = 1 To 100
? ? ? ? ' find the first occurrence of the number in the first row
? ? ? ? Set foundCell = numbersRange.Find(What:=i, LookIn:=xlValues, LookAt:=xlWhole)
? ? ? ? ' write the column number
? ? ? ? columnNumbers(i) = foundCell.Column
? ? Next i
? ??
? ? columnOrder = columnNumbers ' replace with your desired column order
? ? ? ??
? ? ' Copy and reorder columns
? ? For originalColumn = LBound(columnOrder) To UBound(columnOrder)
? ? ? ? targetColumn = originalColumn
? ? ? ? originalSheet.Columns(columnOrder(originalColumn)).Copy Destination:=targetSheet.Columns(targetColumn)
? ? Next originalColumn
? ??
? ? ' Delete original sheet
? ? Application.DisplayAlerts = False ' Suppress alert
? ? Application.DisplayAlerts = True
? ??
? ? ' Activate target sheet
? ? targetSheet.Activate
? ??
? ? ' Clean up objects
? ? Set originalSheet = Nothing
? ? Set targetSheet = Nothing
? ??
End Sub        

As seen below, the correct order of columns has been restored:

CONCLUSION

Managing large Excel data files requires the ability to reorder columns using VBA. Column arrangement can greatly influence data analysis and visualization when dealing with voluminous datasets. With VBA's column reordering capabilities, organizations can customize the structure and layout of their data files to suit specific analysis needs. This enables users to effortlessly navigate and extract meaningful insights from large datasets. In order to enhance the usability and interpretability of large Excel data files, VBA-based column reordering offers a flexible and streamlined way to arrange columns based on logical sequences, prioritize relevant information, or align with specific reporting formats.