Is there a way to write the a subset of values in an array to a range on a
worksheet?

I can use a for next loop which is very reliable but slow, but I don't know
how to set an output range and only get part of the array.

Any help will be greatly appreciated.

Thanks,
Raul

Hi Raul,

Normally, looping in an array is very fast.

Perhaps you could provide more specifics of your requirements.


---
Regards,
Norman



"Raul" wrote in message
...
Is there a way to write the a subset of values in an array to a range on a
worksheet?

I can use a for next loop which is very reliable but slow, but I don't
know
how to set an output range and only get part of the array.

Any help will be greatly appreciated.

Thanks,
Raul

I agree that typically looping in an array is very fast, but I have noticed a
significant difference in performance (on the order of 30 - 60 seconds)
between the two methods listed below. I'm usually dealing with arrays that
are roughly 2000 X 30 or larger, and the first row consists of column names.

I've been using Method 1 for quite a while, and it's really handy because I
can start the looping at row 1 instead of 0 if I want to skip the column
names. This means I can use the same functions to generate the arrays and
simply change the loop index if needed. An example would be when we need to
add information to existing data.

Method 2 is something I'm trying to use to improve the performance of the
process, but I don't know how to skip the first row. I also don't want to
modify the functions that generate the arrays.

Method 1
For i = 0 To UBound(ResultsArray, 1)
For j = 1 To UBound(ResultsArray, 2)
Worksheets(ThisSheet).Cells(OutputRow + i, OutputCol + j - 1).Value =
ResultsArray(i, j)
Next j
Next i

Method 2
Set DestRange = Range(Worksheets(ThisSheet).Cells(OutputRow, OutputCol - 1), _
Worksheets(ThisSheet).Cells(OutputRow + UBound(ResultsArray,
1), _
OutputCol + UBound(ResultsArray, 2) - 1))

DestRange.Value = ResultsArray

Thanks,
Raul


"Norman Jones" wrote:

Hi Raul,

Normally, looping in an array is very fast.

Perhaps you could provide more specifics of your requirements.


---
Regards,
Norman



"Raul" wrote in message
...
Is there a way to write the a subset of values in an array to a range on a
worksheet?

I can use a for next loop which is very reliable but slow, but I don't
know
how to set an output range and only get part of the array.

Any help will be greatly appreciated.

Thanks,
Raul

Hi Raul,

Try something like:

'=============
Public Sub Tester001()
Dim arr As Variant
Dim arr2 As Variant
Dim i As Long, j As Long, k As Long, m As Long

arr = Range("A1").Resize(2000, 30).Value

i = UBound(arr, 1) - LBound(arr, 1) + 1
j = UBound(arr, 2) - LBound(arr, 2) + 1

ReDim arr2(1 To i - 1, 1 To j)

For k = 2 To i
For m = 1 To j
arr2(k - 1, m) = arr(k, m)
Next m
Next k

Sheets.Add
ActiveSheet.Range("A1").Resize(i - 1, j).Value = arr2

End Sub
'<<=============


---
Regards,
Norman


"Raul" wrote in message
...
I agree that typically looping in an array is very fast, but I have noticed
a
significant difference in performance (on the order of 30 - 60 seconds)
between the two methods listed below. I'm usually dealing with arrays
that
are roughly 2000 X 30 or larger, and the first row consists of column
names.

I've been using Method 1 for quite a while, and it's really handy because
I
can start the looping at row 1 instead of 0 if I want to skip the column
names. This means I can use the same functions to generate the arrays and
simply change the loop index if needed. An example would be when we need
to
add information to existing data.

Method 2 is something I'm trying to use to improve the performance of the
process, but I don't know how to skip the first row. I also don't want to
modify the functions that generate the arrays.

Method 1
For i = 0 To UBound(ResultsArray, 1)
For j = 1 To UBound(ResultsArray, 2)
Worksheets(ThisSheet).Cells(OutputRow + i, OutputCol + j - 1).Value =
ResultsArray(i, j)
Next j
Next i

Method 2
Set DestRange = Range(Worksheets(ThisSheet).Cells(OutputRow, OutputCol -
1), _
Worksheets(ThisSheet).Cells(OutputRow +
UBound(ResultsArray,
1), _
OutputCol + UBound(ResultsArray, 2) - 1))

DestRange.Value = ResultsArray

Thanks,
Raul


"Norman Jones" wrote:

Hi Raul,

Normally, looping in an array is very fast.

Perhaps you could provide more specifics of your requirements.


---
Regards,
Norman



"Raul" wrote in message
...
Is there a way to write the a subset of values in an array to a range
on a
worksheet?

I can use a for next loop which is very reliable but slow, but I don't
know
how to set an output range and only get part of the array.

Any help will be greatly appreciated.

Thanks,
Raul

Norman,
Thanks, I hadn't thought of creating a new array.

By the way, what is the proper syntax for

xxx = Range("A1").Resize(2000, 30).Value
or
ActiveSheet.Range("A1").Resize(i - 1, j).Value = arr2

if I want to use row and column indexes instead of "A1" ?

Thanks again,
Raul


"Norman Jones" wrote:

Hi Raul,

Try something like:

'=============
Public Sub Tester001()
Dim arr As Variant
Dim arr2 As Variant
Dim i As Long, j As Long, k As Long, m As Long

arr = Range("A1").Resize(2000, 30).Value

i = UBound(arr, 1) - LBound(arr, 1) + 1
j = UBound(arr, 2) - LBound(arr, 2) + 1

ReDim arr2(1 To i - 1, 1 To j)

For k = 2 To i
For m = 1 To j
arr2(k - 1, m) = arr(k, m)
Next m
Next k

Sheets.Add
ActiveSheet.Range("A1").Resize(i - 1, j).Value = arr2

End Sub
'<<=============


---
Regards,
Norman


"Raul" wrote in message
...
I agree that typically looping in an array is very fast, but I have noticed
a
significant difference in performance (on the order of 30 - 60 seconds)
between the two methods listed below. I'm usually dealing with arrays
that
are roughly 2000 X 30 or larger, and the first row consists of column
names.

I've been using Method 1 for quite a while, and it's really handy because
I
can start the looping at row 1 instead of 0 if I want to skip the column
names. This means I can use the same functions to generate the arrays and
simply change the loop index if needed. An example would be when we need
to
add information to existing data.

Method 2 is something I'm trying to use to improve the performance of the
process, but I don't know how to skip the first row. I also don't want to
modify the functions that generate the arrays.

Method 1
For i = 0 To UBound(ResultsArray, 1)
For j = 1 To UBound(ResultsArray, 2)
Worksheets(ThisSheet).Cells(OutputRow + i, OutputCol + j - 1).Value =
ResultsArray(i, j)
Next j
Next i

Method 2
Set DestRange = Range(Worksheets(ThisSheet).Cells(OutputRow, OutputCol -
1), _
Worksheets(ThisSheet).Cells(OutputRow +
UBound(ResultsArray,
1), _
OutputCol + UBound(ResultsArray, 2) - 1))

DestRange.Value = ResultsArray

Thanks,
Raul


"Norman Jones" wrote:

Hi Raul,

Normally, looping in an array is very fast.

Perhaps you could provide more specifics of your requirements.


---
Regards,
Norman



"Raul" wrote in message
...
Is there a way to write the a subset of values in an array to a range
on a
worksheet?

I can use a for next loop which is very reliable but slow, but I don't
know
how to set an output range and only get part of the array.

Any help will be greatly appreciated.

Thanks,
Raul

Hi Raul,

By the way, what is the proper syntax for
xxx = Range("A1").Resize(2000, 30).Value

I could have used the equivalent:

Arr = range("A1:AD2000")

I used the original sytax (which, by the way, is perfectly proper!)
merely to emphasise your information:

I'm usually dealing with arrays that are roughly 2000 X 30
or larger,


---
Regards,
Norman

Norman,
I did not mean to imply that your syntax was incorrect.

I was asking for the syntax to use if I wanted to refer to the range using
something like the following pseudocode:

xxx = Range(cell(RowNum,ColNum)).Resize(NumOfRows, NumOfCols).Value

I like to use row and column indexes to make this code snippet more flexible.

Thanks,
Raul




"Norman Jones" wrote:

Hi Raul,

By the way, what is the proper syntax for
xxx = Range("A1").Resize(2000, 30).Value

I could have used the equivalent:

Arr = range("A1:AD2000")

I used the original sytax (which, by the way, is perfectly proper!)
merely to emphasise your information:

I'm usually dealing with arrays that are roughly 2000 X 30
or larger,


---
Regards,
Norman

Hi Raul,

Try:

arr = Range(Cells(1, 1), Cells(2000, 30)).Value


---
Regards,
Norman



"Raul" wrote in message
...
Norman,
I did not mean to imply that your syntax was incorrect.

I was asking for the syntax to use if I wanted to refer to the range using
something like the following pseudocode:

xxx = Range(cell(RowNum,ColNum)).Resize(NumOfRows, NumOfCols).Value

I like to use row and column indexes to make this code snippet more
flexible.

Thanks,
Raul




"Norman Jones" wrote:

Hi Raul,

By the way, what is the proper syntax for
xxx = Range("A1").Resize(2000, 30).Value

I could have used the equivalent:

Arr = range("A1:AD2000")

I used the original sytax (which, by the way, is perfectly proper!)
merely to emphasise your information:

I'm usually dealing with arrays that are roughly 2000 X 30
or larger,


---
Regards,
Norman

Thank you very much!
Raul


"Norman Jones" wrote:

Hi Raul,

Try:

arr = Range(Cells(1, 1), Cells(2000, 30)).Value


---
Regards,
Norman



"Raul" wrote in message
...
Norman,
I did not mean to imply that your syntax was incorrect.

I was asking for the syntax to use if I wanted to refer to the range using
something like the following pseudocode:

xxx = Range(cell(RowNum,ColNum)).Resize(NumOfRows, NumOfCols).Value

I like to use row and column indexes to make this code snippet more
flexible.

Thanks,
Raul




"Norman Jones" wrote:

Hi Raul,

By the way, what is the proper syntax for
xxx = Range("A1").Resize(2000, 30).Value

I could have used the equivalent:

Arr = range("A1:AD2000")

I used the original sytax (which, by the way, is perfectly proper!)
merely to emphasise your information:

I'm usually dealing with arrays that are roughly 2000 X 30
or larger,


---
Regards,
Norman