Sorting a two dimensional array in Excel VBA based on two columns, can be completed fairly easily using the code below.

how to swap columns in excel

The code assumes that your array starts at 0. So, in other words you have not declared Option Base = 1.

Where it says Sort1=1 this is referring to the array and specifying that you wish to sort the array by the second column which is 1.

Where it says Sort2=7 this is referring to the array and specifying that you wish to sort the array by the eighth column, which is 7.

Remember, in Excel arrays the rows and columns start at 0, unless at the very top of the code you write Option Base 1. Having this at the top will force an array to start at 1 and not 0. So that when you reference an array element, you call it by its actual location, for example (1,2). This could be (row 1, column 2) depending on how your array is set up. Whereas, without the Option Base 1 at the top you would reference the

previous element as (0,1).

When you see > this is what specifys the ascending sort. To sort descending simply swap > with <. For example:

Condition1= YourArrayName (Y, Sort1) > YourArrayName (Y + 1, Sort1) – Is ascending

Condition1= YourArrayName (Y, Sort1) < YourArrayName (Y + 1, Sort1) – Is descending

The LBound(YourArrayName,1) specifies the lowest possible value in the array. In this case it would be zero.

The UBound(YourArrayName,1) specifies the highest possible value in the array. So this would be the number of the last element in the array.

Sort1=1

Sort2=7

For X = LBound(YourArrayName, 1) To UBound(YourArrayName, 1) - 1

For Y = LBound(YourArrayName, 1) To UBound(YourArrayName, 1) - 1

Condition1= YourArrayName (Y, Sort1) > YourArrayName (Y + 1, Sort1)

Condition2= YourArrayName (Y, Sort1) = YourArrayName (Y + 1, Sort1) And _

YourArrayName (Y, Sort2) > YourArrayName (Y + 1, Sort2)

If Condition1 or Condition2 then

Source: www.excelgeek.co.uk

Category: Forex

Similar articles: