Visual Basic Application (VBA) is the programming language that Excel incorporates into its Macro editor. The VBE (Visual Basic Editor) Macro editor in Microsoft Excel allows users to write simple programs that automate redundant tasks. Here we will write a VBA Macro that will duplicate data in a worksheet, sort it in a multidimensional array and write it to a new work sheet. This process is useful when a sorted worksheet is needed, but the original, unsorted worksheet must remain intact.
- Skill level:
Other People Are Reading
Launch Excel by clicking "Start," "Microsoft Office Suite," "Excel."
Launch the Visual Basic Editor (VBE) by pressing "ALT" and "F11" simultaneously.
Write the process for copying a worksheet into a multidimensional array. Select the following code and copy it into the VBE:
Dim arrData() As Variant
Dim ColACount As Long
Dim I As Long
ColACount = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Count
ReDim arrData(1 To ColACount, 1 To 2)
For I = 1 To ColACount
arrData(i, 1) = Range("A" & i).Value arrData(i, 2) = Range("B" & i).Value
Sort the array with the VBA Bubble Sort process. Copy and paste the following code below the code from the previous step. It will sort the two dimensional array:
For I = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
For j = LBound(ArrData, 1) To UBound(ArrData, 1) - 1 Condition1=ArrData(j, SortColumn1) > ArrData(j + 1, SortColumn1) Condition2=ArrData(j, SortColumn1) = ArrData(j + 1, SortColumn1) and _ ArrData(j, SortColumn2) > ArrData(j + 1, SortColumn2) If Condition 1 or Condition 2 then For y = LBound(ArrData, 2) To UBound(ArrData, 2) t = ArrData(j, y) ArrData(j, y) = ArrData(j + 1, y) ArrayName(j + 1, y) = t Next y End If Next Next
Create the new spreadsheet that will contain the sorted data. Copy and paste the following code below the code listed thus far:
Dim WS as Worksheet
Set WS = Sheets.Add
Copy the array to the new worksheet. Copy and paste the following code below the code created above:
[a1].Resize(UBound(MyArr), UBound(Application.Transpose(MyArr))) = MyArr
Save the Macro. Click "File," "Save," give it a name and click "Save."
Run the Macro. Open a Worksheet that you would like to copy and sort. Click "Tools," "Macros," select the macro you just created. The new worksheet will appear and populate automatically.
Tips and warnings
- The example code here will sort the array in ascending order (a-z, 0-9) To reverse the order of the sort, change the ">" in the fifth and seventh lines of step four to "<".
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for