• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

Paste 2x2 array

Hello-- I am retrieving data from a database using embedded sql code in VBA and storing it in a 2x2 array. I need to paste it all into spreadsheet and right now am just doing it with a nested for-loop:

For i = 0 To UBound(dbdata,2)
    For j = 0 To UBound(dbdata)
        ActiveSheet.Cells(i+1,j+1).Value = dbdata(j,i)
    Next
Next

But it is a tremendous array and it's a pretty long run-time to insert each value in the array into the spreadsheet one by one. Is there a way to just paste the entire array into the spreadsheet in one fowl swoop, ie something along the lines of dbdata.paste [with dbdata(1,1) at ActiveSheet.Cells(1,1)]?

Thanks.
0
Jeff9687
Asked:
Jeff9687
  • 3
1 Solution
 
StephenJRCommented:
Does this work?
ActiveSheet.Cells(1,1).resize(ubound(dbdata,1),ubound(dbdata,2)).Value=dbdata

Open in new window

0
 
Rory ArchibaldCommented:
You'll need to transpose the data into another array first, then use Stephen's code.
0
 
Rory ArchibaldCommented:
Or use CopyFromRecordset instead of GetRows which I guess is how you are getting the data into an array in the first place?
0
 
Rory ArchibaldCommented:
Without any more information, it would appear that a points split was in order here?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now