CODEWITHSUNDEEP
arraysexcelvbareplace
Stephane Maarek
Stephane Maarek

Reputation: 5352

Excel VBA: Replace first row by an array

I have a first row with 100 cells and I created an Array of Strings, which represent the new row content. I would like to replace the content of all the first row with the content of my Array in VBA, how can I do that?

Upvotes: 0

Views: 857

Answers (2)

Paul Kelly
Paul Kelly

Reputation: 985

You can read and write between a Range and an Array in one line. It is more efficient than using a loop.

Note: The array must be 2 Dimensional to write to a range. 

Public Sub ReadToArray()

    ' Create dynamic array
    Dim StudentMarks() As Variant

    ' Read values into array from 100 cells in row 1
    StudentMarks = Sheets("Sheet1").Range("A1:CV1").Value

    ' Do something with array

    ' Write the values back to sheet
    Sheets("Sheet1").Range("A1:CV1").Value = StudentMarks

End Sub

Upvotes: 1

Matteo NNZ
Matteo NNZ

Reputation: 12695

Say your array is called myArray, it's enough to do this:

For j = LBound(myArray) To UBound(myArray)
    Sheets("your sheet").Cells(1,j+1).Value = myArray(j)
Next j

The functions LBound() and UBound() are respectively returning the first and the last index of your array.

Please note that when writing Cells(1,j+1) I'm assuming two important things:

1) Your start index starts with 0, so I want to start the insertion of the values from the column 1 (j+1 = 0+1 = 1).

2) You want to override the first row (because the row index is equal to 1).

You might want to customize this, for example creating independent indexes - when I say "independent", I mean "not depending on the lower and the upper bound of your array, nor being hard-coded like I did for the "row 1".

Upvotes: 1

Related Questions