[Webinar] Streamline your web hosting managementRegister Today

x
  • 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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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