How to duplicate data & sort a VBA multidimensional array in excel

Written by christopher hanson
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to duplicate data & sort a VBA multidimensional array in excel
VBA automates redundant Excel tasks. (ANSI image by DBX 60 from Fotolia.com)

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:
Moderate

Other People Are Reading

Instructions

  1. 1

    Launch Excel by clicking "Start," "Microsoft Office Suite," "Excel."

  2. 2

    Launch the Visual Basic Editor (VBE) by pressing "ALT" and "F11" simultaneously.

  3. 3

    Write the process for copying a worksheet into a multidimensional array. Select the following code and copy it into the VBE:

    Sub Read_Into_Array()

    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
    

    Next i

    End Sub

  4. 4

    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:

    Sub Sort_Array()

    SortColumm1=0

    SortColumn2=3

    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
    

    End Sub

  5. 5

    Create the new spreadsheet that will contain the sorted data. Copy and paste the following code below the code listed thus far:

    Sub New_Worksheet()

    Dim WS as Worksheet

    Set WS = Sheets.Add

    End Sub

  6. 6

    Copy the array to the new worksheet. Copy and paste the following code below the code created above:

    Sub Copy_Array()

    [a1].Resize(UBound(MyArr), UBound(Application.Transpose(MyArr))) = MyArr

    End Sub

  7. 7

    Save the Macro. Click "File," "Save," give it a name and click "Save."

  8. 8

    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 "<".

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.