Excel-VBA – Insert new first column in datafield array without loops or API calls

by

Found solution via the Application.Index function

I found a solution simply by trying out some unusual variations of the Application.Index function which I try to resume as a comprehensive generic overview to demonstrate the rich range of application. So any helpful addition is welcome (c.f. @chrisneilsen ‘s comment).

Some peculiarities of the the Application.Index function

Typically the index function would deliver a well defined item by its row and column position, but there are some not so widely known pecularities:

  1. Similarly to the Worksheet.Index function you can get the entire column or row items if the row or column number argument is set to zero (0). – Another frequently unknown way to create a 2-dim array by passing a double-zero parameter can be found at How to initialize a 2-dim array in Excel VBA

  2. Use of array arguments possible – This function allows not only the known index indications by given numbers, but also array parameters to extract “rows” or “columns”, so it’s possible to indicate a wanted set of columns, e.g.A:C via Array(1,2,3) as column array argument.

  3. Filtering effects – Furthermore I learnt that it is possible to reduce the choice to some columns (rows) only, e.g. via Array(1,3) and even to change the internal order, e.g. Array(3,2,1)`.

  4. Restructuring – The most surprising fact, however, is that it is possible to repeat a column choice, e.g. via Array(1,1,2,3)
    or even Array(0,1,2,3) where the 0 item is the same as column 1. This can be used to reach the same effect as a column insertion.

This last restructuring capability of the mentioned Index function is the key part of my approach:

Code example

Sub AddFirstIndexColumn()
  Dim v, i&, ws As Worksheet
  Set ws = ThisWorkbook.Worksheets("SourceSheet")  ' << change to source sheet name
' [1] get data
      v = ws.[A1:C5].Value2
' [2] define column array inserting first column (0 or 1) and preserving old values (1,2,3)
      v = Application.Index(v, _
          Application.Evaluate("row(1:" & UBound(v) & ")"), _
          Array(0, 1, 2, 3))   ' columns array where 0 reinserts the first column
' [3] add an current number in the first column
      For i = LBound(v) To UBound(v): v(i, 1) = i: Next i
 End Sub

How to test the result

Just insert the following to the code above:

' [4a] test result by debugging in immediate window
      For i = LBound(v) To UBound(v)
          Debug.Print "#" & i & ": " & Join(Application.Index(v, i, 0), ", ")
      Next i
' [4b] test result by writing back to target sheet
      Dim ws2 As Worksheet
      Set ws2 = ThisWorkbook.Worksheets("TargetSheet")  ' << change to target sheet name
      ws2.Range("A1").Resize(UBound(v), UBound(v, 2)).Offset(0, 0) = v

Caveat

The found solution seems to be restricted to 65536 rows (possibly similar to array transposing limitation), so that you can’t use it for bigger data.

Some recent Application.Index examples

More Related Contents:

  1. Creating an Array from a Range in VBA
  2. Expanding column cells for each column cell
  3. How do I slice an array in Excel VBA?
  4. Check if a value is in an array or not with Excel VBA
  5. Matching values in string array
  6. Deleting Elements in an Array if Element is a Certain value VBA
  7. Is it possible to fill an array with row numbers which match a certain criteria without looping?
  8. How to automatically input an array formula as string with more than 255 characters in length into an excel cell using VBA?
  9. Return Index of an Element in an Array Excel VBA
  10. Writing an array to a range. Only getting first value of array
  11. Excel VBA: Range to String Array in 1 step
  12. Faster way of using Sumifs()
  13. How to initialize a multidimensional array variable in vba for excel
  14. VBA using ubound on a multidimensional array
  15. Turn Excel range into VBA string
  16. Get values from union of non-contiguous ranges into array with VBA with a simple command (no loops)
  17. Why am I having issues assigning a Range to an Array of Variants
  18. Subscript out of range error with an array – no idea why?
  19. VBA Excel large data manipulation taking forever
  20. Performance difference between looping range vs looping array
  21. Create Chart from Array data and not range
  22. Excel VBA Quickest way to sort an array of numbers in descending order?
  23. Simple VBA array join not working
  24. Excel VBA – How to Redim a 2D array?
  25. ReDim Preserve “Subscript Out of Range”
  26. Using transpose versus ctranspose in MATLAB
  27. How to search for string in an array
  28. Sorting a multidimensionnal array in VBA
  29. Convert a matrix to a 1 dimensional array
  30. Combining matrices into an array in R

Leave a Comment