Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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