CODEWITHSUNDEEP
excelvba
abc
abc

Reputation: 429

adding new rows of data to 2d array and paste it back to worksheet

After assigning a table with 10 rows and 5 columns of data to an array, I wish to add new rows of data to the array before pasting it back to the same worksheet.

Despite days of googling, I'm still having difficulty grasping the concept of 2d arrays so my code below isn't working as intended.

Sub testing_array()

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")

'transposed array with 10 rows and 5 columns of data
Dim myarray As Variant: myarray = WorksheetFunction.Transpose(ws.Range("A1").CurrentRegion)

'increase array to 100 rows, 5 columns
ReDim Preserve myarray(1 To 5, 1 To 100)

'assigning values to 11th row of array
myarray(1, 11) = "A"
myarray(2, 11) = "B"
myarray(3, 11) = "C"
myarray(4, 11) = "D"
myarray(5, 11) = "E"

'undo transposing
myarray = WorksheetFunction.Transpose(myarray)

'resize and paste updated array back to worksheet
ws.Range("A1").Resize(UBound(myarray) + 1).value = Application.Transpose(a)

End Sub

Upvotes: 0

Views: 97

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Try it in this way, please:

Sub testing_array()
 Dim ws As Worksheet: Set ws = ActiveSheet ' ThisWorkbook.Worksheets("Sheet1")

 'transposed array with 10 rows and 5 columns of data
 Dim myarray As Variant: myarray = WorksheetFunction.Transpose(ws.Range("A1").CurrentRegion)

 'increase array to 15 rows, 5 columns
 ReDim Preserve myarray(1 To 5, 1 To 15)

 'assigning values to 11th row of array
 myarray(1, 11) = "A"
 myarray(2, 11) = "B"
 myarray(3, 11) = "C"
 myarray(4, 11) = "D"
 myarray(5, 11) = "E"

 'You must drop the array content IN A RANGE EXACTLY RESIZED AS THE ARRAY TO BE USED,
 'in terms of number of rows and columns
 'Because of transposing, the rows and columns are reversed in terms of UBound returns:
 ws.Range("A1").Resize(UBound(myarray, 2), UBound(myarray)).value = Application.Transpose(myarray)
End Sub

If something (still) not clear, plese do not hesitate to ask for clarification.

Edited:

Pleas, look to the next code, showing how to increase the array rows, WHEN YOU DO NOT KNOW how many rows to be added:

Sub testing_array_2()
 Dim ws As Worksheet: Set ws = ActiveSheet ' ThisWorkbook.Worksheets("Sheet1")

 'transposed array with 10 rows and 5 columns of data
 Dim myarray As Variant: myarray = WorksheetFunction.Transpose(ws.Range("A1").CurrentRegion)
 
 Dim iRow As Long, jCol As Long, i As Long, k As Long
 iRow = UBound(myarray, 2): jCol = UBound(myarray)
 
 'increase array to all rows, all columns columns, to increase with OTHER 100 rows
 ReDim Preserve myarray(1 To jCol, 1 To iRow + 100)

 'assigning values to i rows of array. It can be done from another reange, NOT KNOWING HOW MANY ROWS IT CONTAINS:
 For i = 1 To 10 ' 10 only like an example
    k = iRow + i 'this is the variable to be used for the final Redim Preserve
    myarray(1, k) = "A" & i
    myarray(2, k) = "B" & i
    myarray(3, k) = "C" & i
    myarray(4, k) = "D" & i
    myarray(5, k) = "E" & i
 Next i
 ReDim Preserve myarray(1 To jCol, 1 To iRow + k)
 
 'You must drop the array content IN A RANGE EXACTLY RESIZED AS THE ARRAY TO BE USED
 'in terms of number of rows and columns
 'Because of transposing, the rows and columns are reversed in terms of UBound returns:
 ws.Range("A1").Resize(UBound(myarray, 2), UBound(myarray)).value = Application.Transpose(myarray)
End Sub

Upvotes: 1

Related Questions