How to add a counter column to existing matrix in VBA?

Question

How to get a new matrix in VBA with a counter value in the first "column". Suppose we have a VBA matrix which values we get from cells. The value of A1 cell is simply "A1".

Dim matrix As Variant
matrix = Range("A1:C5").value

Input matrix:

+----+----+----+
| A1 | B1 | C1 |
+----+----+----+
| A2 | B2 | C2 |
+----+----+----+
| A3 | B3 | C3 |
+----+----+----+
| A4 | B4 | C4 |
+----+----+----+
| A5 | B5 | C5 |
+----+----+----+

I would like to get new matrix with the counter value in the first column of VBA matrix.

Here are desired results:

+----+----+----+----+
|  1 | A1 | B1 | C1 |
+----+----+----+----+
|  2 | A2 | B2 | C2 |
+----+----+----+----+
|  3 | A3 | B3 | C3 |
+----+----+----+----+
|  4 | A4 | B4 | C4 |
+----+----+----+----+
|  5 | A5 | B5 | C5 |
+----+----+----+----+

One way to do it is looping. Would there be any other more elegant way to do it? We are dealing here with large data sets, so please mind the performance.

You'll need to loop here. Do you really need the counter though? It just has the same values as the index of the "row" dimension... — Tim Williams, Oct 12 '17 at 15:53
@TimWilliams I need it for the reason turning this matrix into one long string. Why? Please see here https://stackoverflow.com/questions/39597999/turn-excel-range-into-vba-string — Przemyslaw Remin, Oct 13 '17 at 07:25
Since you need to loop anyway, can't you use the loop counter for the 1,2,etc you show in the first column? — Tim Williams, Oct 13 '17 at 15:50
@PrzemyslawRemin: Thomas' code in the referred question https://stackoverflow.com/questions/39597999/turn-excel-range-into-vba-string#answer-39781372 is excellent and you should branch it to handle your new use case (and it loops). Also, Tim Williams is right that you do not need the extra column if the values within are a computable sequence. — S Meaden, Oct 18 '17 at 13:39
Found an alternative approach for array column insertion **without loops** which might interest you for principal reasons (caveat: restricted to approximately 65k rows) - see [Insert new first column in datafield array without loops](https://stackoverflow.com/questions/51688593/excel-vba-insert-new-first-column-in-datafield-array-without-loops-or-api-call?noredirect=1&lq=1) — T.M., Aug 06 '18 at 19:14

Florent B. · Accepted Answer · 2017-10-19T20:37:00.363

9

If your main concern is the performance, then use Redim Preserve to add a new column at the end and use the OS API to shift each column directly in the memory:

Private Declare PtrSafe Sub MemCpy Lib "kernel32" Alias "RtlMoveMemory" ( _
  ByRef dst As Any, ByRef src As Any, ByVal size As LongPtr)

Private Declare PtrSafe Sub MemClr Lib "kernel32" Alias "RtlZeroMemory" ( _
  ByRef src As Any, ByVal size As LongPtr)


Sub AddIndexColumn()
  Dim arr(), r&, c&
  arr = [A1:F1000000].Value

  ' add a column at the end
  ReDim Preserve arr(LBound(arr) To UBound(arr), LBound(arr, 2) To UBound(arr, 2) + 1)

  ' shift the columns by 1 to the right
  For c = UBound(arr, 2) - 1 To LBound(arr, 2) Step -1
    MemCpy arr(LBound(arr), c + 1), arr(LBound(arr), c), (UBound(arr) - LBound(arr) + 1) * 16
  Next
  MemClr arr(LBound(arr), LBound(arr, 2)), (UBound(arr) - LBound(arr) + 1) * 16

  ' add an index in the first column
  For r = LBound(arr) To UBound(arr)
    arr(r, LBound(arr, 2)) = r
  Next

End Sub

edited Oct 19 '17 at 20:37

answered Oct 19 '17 at 16:12

Florent B.

41,537
7
86
101

1

Congratulations, extremely fast. BTW, are there caveats and what's the difference to API methods like CopyMemory? – T.M. Oct 19 '17 at 17:53
2

@T.M., the caveat is that it uses the native API which is different on Mac OS. For the different between `CopyMemory` and `MoveMemory`, have a look here: https://msdn.microsoft.com/en-us/library/windows/desktop/ff561808(v=vs.85).aspx – Florent B. Oct 19 '17 at 18:07
You multiplied size by 16, can you explain why multiplying by 16? – Dy.Lee Nov 08 '20 at 02:09
1

@Dy.Lee, a [variant](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary) is 16 bytes. Memory size = count * 16 – Florent B. Nov 08 '20 at 02:16
Thank you for your response. I learned another one today. – Dy.Lee Nov 08 '20 at 05:14

score 3 · Answer 2 · answered Oct 17 '17 at 07:39

3

Method 1

This method inserts cells to the left of the range and set the new cells formula to calculate the counter =ROWS($A$1:$A5). Note: this pattern is also used to calculate a running total.

Usage

InsertCounter Worksheets("Sheet1").Range("A1:C5")

Sub InsertCounter(Target As Range)
    Dim counterCells As Range
    Target.Columns(1).Insert Shift:=xlToRight
    Set counterCells = Target.Columns(1).Offset(0, -1)
    counterCells.Formula = "=Rows(" & counterCells.Cells(1, 1).Address(True, True) & ":" & counterCells.Cells(1, 1).Address(False, True) & ")"
End Sub

Method 2

This method copies the Ranges' Values into an array, creates a new array with 1 extra column and then copies the data and a counter over to the new array. The difference in this Method is that it doesn't insert any cells.

Usage

AddCounterToMatrix Worksheets("Sheet1").Range("A1:C5")

Sub AddCounterToMatrix(Target As Range)
    Dim x As Long, y As Long
    Dim Matrix1 As Variant, NewMatrix1 As Variant
    Matrix1 = Target.Value

    ReDim NewMatrix1(LBound(Matrix1) To UBound(Matrix1), LBound(Matrix1, 2) To UBound(Matrix1, 2) + 1)

    For x = LBound(Matrix1) To UBound(Matrix1)
        NewMatrix1(x, 1) = x - LBound(Matrix1) + 1
        For y = LBound(Matrix1, 2) To UBound(Matrix1, 2)
            NewMatrix1(x, y + 1) = Matrix1(x, y)
        Next
    Next

    Target.Resize(UBound(NewMatrix1) - LBound(Matrix1) + 1, UBound(NewMatrix1, 2) - LBound(NewMatrix1, 2) + 1).Value = NewMatrix1

End Sub

answered Oct 17 '17 at 07:39

Is there no possibility to suck the whole matrix in one move? Something like `NewMatrix(Range(1,2);Range(Upper,Upper)=OldMatrix` ? You are looping through every value of OldMatrix. – Przemyslaw Remin Oct 17 '17 at 08:19
You can not expand an array to the left. You could break the matrix up into single columns and use `WorksheetFunction.Transpose` to recombine them but this is far more complex then simply looping through every element of the array, It would be slightly more efficient to resize the Target Range to include an extra column. In this way, you would not have to have a 2nd matrix in memory. The difference, however, would is negligible. – Oct 17 '17 at 15:22
Is it possible to expand matrix to the right and preserve all the values? So that the counter column would be as the last one? Then it is just the matter to stuff the counter column with values. – Przemyslaw Remin Oct 18 '17 at 07:48
Yes, `Redim Preserve` will do this. Reference: [dynamic-array-with-redim-preserve](https://wellsr.com/vba/2016/excel/dynamic-array-with-redim-preserve-vba/). – Oct 18 '17 at 08:35
That site says "The primary limitation of the ReDim Preserve statement is it can only change the upper bound". This is misleading. You can only change the last dimension of an array. You can actually change the lower or upper bound on the last dimension using ReDim Preserve. The data will, however, stay in the upper left hand corner of the array. – Oct 18 '17 at 08:40
Reducing the size of the last dimension using ReDim Preserve will erase any data that doesn't fit into the newly resized array. – Oct 18 '17 at 08:43

score 1 · Answer 3 · answered Oct 16 '17 at 14:09

1

using a Dynamic variant is fast.

Sub test()
    Dim matrix As Variant, newMatrix()
    Dim i As Long, n As Long, c As Long, j As Long
    matrix = Range("A1:C5").Value
    n = UBound(matrix, 1)
    c = UBound(matrix, 2)
    ReDim newMatrix(1 To n, 1 To c + 1)
    For i = 1 To n
        newMatrix(i, 1) = i
        For j = 2 To c + 1
            newMatrix(i, j) = matrix(i, j - 1)
        Next j
    Next i
    Range("a1").Resize(n, c + 1) = newMatrix
End Sub

answered Oct 16 '17 at 14:09

Dy.Lee

7,527
1
12
14

Thank you. I would like to avoid looping through the initial matrix as you have done in your proposal. I am looking for declaring new matrix with counter and gluing it somehow to initial matrix. Your solution won't be fast if data grows to one million cells. – Przemyslaw Remin Oct 16 '17 at 14:16

score 0 · Answer 4 · answered Oct 16 '17 at 10:37

0

excel based solution are ok for u?

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1") = "1"
Range("A2") = "2"
Range("A1:A2").Select
Selection.AutoFill Destination:=Range("A1:A5")

Dim matrix As Variant
matrix = Range("A1:D5").Value

answered Oct 16 '17 at 10:37

tranyo

19
2

No, modifying Excel sheets is something I do not want. VBA coding only. – Przemyslaw Remin Oct 16 '17 at 12:56

score 0 · Answer 5 · answered Oct 17 '17 at 09:15

0

Why not a compromise between household remedies and pure array scripting by inserting a temporary column and doing the rest within the array's first column.

Code

Option Explicit

Public Sub test_CounterCol2()
Dim matrix As Variant, newMatrix()
Dim i As Long, n As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("CounterCol")    ' <== user defined sheet
' a) insert column temporarily]
  ws.Columns("A:A").Insert Shift:=xlToRight
' b) get values
  matrix = ws.Range("A1:D5").value
' c) only loop within array counter column
  n = UBound(matrix, 1)
  For i = 1 To n
        matrix(i, 1) = i
  Next i
' d) delete temporary insertion
  ws.Columns("A:A").Delete (xlShiftToLeft)

End Sub

Additional note: Maybe you can find something via API (CopyMemory).

answered Oct 17 '17 at 09:15

T.M.

9,436
3
33
57

Let me piggyback your answer and say that one can hide the counter column so there's no need to insert and delete it. – S Meaden Oct 18 '17 at 13:31
@SMeaden, think this doesn't meet the OP needs. Just to be clear anyway: the temporary column is necessary, because it is read into the variant datafield array with this empty first array "column" to start with and the contents of the three data "columns". – T.M. Oct 18 '17 at 15:43
@T,M. yeah I know full well that the extra column gives the extra room. thanks. Your answer's approach would be the approach I would take but I wasn't bothered to write a separate competing answer. I thought I'd get behind your answer. – S Meaden Oct 18 '17 at 15:55

How to add a counter column to existing matrix in VBA?

5 Answers5

Method 1

Usage

Method 2

Usage

Linked