VBA Using Ranges with Arrays

[johnclimer]

Hello,

I have an array of ranges and I want to know if there is a way to paste one row from one of the ranges to a range in the active worksheet?

Here is what my code looks like:

For i = 1 to 10
  dataArray(i) = Range("C1:T50")
Next i
 
ActiveSheet.Range("C1:T1") = dataArray(i)(???)

I can access individual cells of dataArray with no problem: dataArray(i)(1,1)

or the entire range: dataArray(i)

but I want only a 'row' from the range.

Any suggestions???

Thanks,
John Climer

 

[shg]

Welcome to the board.

Maybe ...

ActiveSheet.Range("C1:T1").Value = WorksheetFunction.Index(dataArray(i), 1, 0)

... but why are you reading the same data into 10 elements of the array?

 

[johnclimer]

In the code posted I simplified what I was doing. I am actually opening different files and inserting ranges from each file into one element of the array.

Here is more complete code of first part;

For i = LBound(Filename) To UBound(Filename)
      msg = msg & Filename(i) & vbCrLf ' This can be removed
      Workbooks.OpenText Filename(i), DataType:=xlDelimited,Space:=True
      numFiles = numFiles + 1
      numRows(i) = Range("A1").CurrentRegion.rows.Count
      dataArray(i) = Range("C1:T" & numRows(i)).value                    
      If (numRows(i) > maxRows) Then maxRows = numRows(i)     
Next i

 

[Rick Rothstein]

Here is what my code looks like:

For i = 1 to 10
  dataArray(i) = Range("C1:T50")
Next i
 
ActiveSheet.Range("C1:T1") = dataArray(i)(???)

I can access individual cells of dataArray with no problem: dataArray(i)(1,1)

or the entire range: dataArray(i)

Since there is nothing variable about the range your code is assigning, it ends up simply assigning the one range ten times, one copy to each element of the array. You can just assign the range directly to a Variant variable which will make it simpler to work with.

Dim dataArray As Variant
dataArray = Range("C1:T50")
' For example...
ThirdElementDownFifthOver = dataArray(3, 5)

I have an array of ranges and I want to know if there is a way to paste one row from one of the ranges to a range in the active worksheet?

...but I want only a 'row' from the range.

VB arrays do not provide a mechanism to slice out a row or column... normally you would just iterate through the rows while holding the column fixed via a For..Next loop. However, there is a way to slice out the row from the array created with the assignment I showed above...

Range("C1").Resize(, UBound(dataArray, 2)) = WorksheetFunction.Index(dataArray, RowPositionNumber, 0)

where RowPositionNumber is the row position in the range (1 being the first row of the assigned range even if the range started on a different row).

 

[johnclimer]

I think using the index function will work.

Thanks a lot.

John

 

[jim may]

I came up with something like this...

Sub Foo()
Dim DataArray()
DataArray = Range("C1:T50")
BegCol = Range("C1").Column
NumCols = UBound(DataArray, 2) + BegCol
For i = BegCol To NumCols - 1
Cells(100, i).Value = DataArray(BegCol, i - 2)  'Paste to Row 100
Next i
End Sub