Manipulate data in array and insert into worksheet

[Stevenn]

I'm loading data from a list object into memory with

Dim vtArray as Variant

Set vtArray = ActiveSheet.ListObjects(1).DataBodyRange

Can I choose a more efficient data type than Variant?

I need to set the value of one of the columns to 0 in all rows. I can do this with

Let ActiveSheet.ListObjects(1).ListColumns(7).Value = 0

before loading the range into the array. But isn't it faster to operate directly in the array rather than the worksheet?

When I'm done comparing and modifying the array, I want to insert the 7th column in the array into the 7th column in the sheet. How can I do this efficiently?

 

[Domenic]

In your example, since you're using the Set statement, you're actually assigning a range to your variable instead of an array. Once you've transferred the values to your variable, you can loop through the 7th column to set your values to zero. However, since you'll be transferring the calculated values from your 7th column of the array back to your worksheet, it would be faster to set the values in your worsheet to zero in the first place, as you've already shown. So maybe something like this...

[COLOR=darkblue]Dim[/COLOR] vtArray [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]

ActiveSheet.ListObjects(1).ListColumns(7).DataBodyRange.Value = 0

vtArray = ActiveSheet.ListObjects(1).DataBodyRange.Value

[COLOR=green]'Do stuff[/COLOR]
'
'

ActiveSheet.ListObjects(1).ListColumns(7).DataBodyRange.Value = Application.Index(vtArray, 0, 7)

Note that there's a limit of 65,536 rows when using the Index method of the Application object to transfer an array to the worksheet.

Hope this helps!

 

[Stevenn]

I receive a type mismatch error even though I haven't exceeded the limit of 65k rows.

I'm doing exactly as you wrote and UBound(vtArray) returns 1775, so the limit has not been hit.

What can cause this error?

 

[Domenic]

Can you post the complete and exact code that you're using?

Also, can you specify which line of code causes the error?

 

[Stevenn]

Can you post the complete and exact code that you're using?

Also, can you specify which line of code causes the error?

I have specified my array with

Dim vtArray As Variant

vtArray = Sheets(1).ListObjects(1).DataBodyRange.Value2

I'm modifying values in my array and finally inserting the new values from the array to the sheet

Let Sheets(1).ListObjects(1).ListColumns(7).DataBodyRange.Value2 = Application.WorksheetFunction.Index(vtArray, 0, 7)

It works with fewer rows so I wonder why I am hitting the limit even though I got so few rows.

 

[Domenic]

Can you confirm which line of code causes the error? When you hit Debug, which line gets highlighted?