Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Copying whole array in a column in single command

  Asked By: Pedro    Date: Oct 18    Category: MS Office    Views: 925
  

Q1: I have an array b in VBA with 10000 elements. I need to write it in
column A of worksheet (cells(1,1) to cells(10000,1)).
By writing element by element, it takes lot of computational time.
for i=1 to 10000
cells(i,1)=b(i)
Next

Is there a command which can enable writing the whole 10000 element
array in column A, 10000 rows directly like
Range(cells(1,1),cells(10000,1))=b or something like that.


Share: 

 

5 Answers Found

 
Answer #1    Answered By: Luigi Fischer     Answered On: Oct 18

Range(Cells(1, 1),Cells(1 + uBound(Array), 1)).Value = Array

This is an edited piece of code from a module I use within Access to
export recordset information to Excel. I haven't tested it directly in
Excel, but it should put you on the right path.

 
Answer #2    Answered By: Latasha Wilson     Answered On: Oct 18

Range(Cells(1, 1), Cells(10000, 1)) =
Application.WorksheetFunction.Transpose(b)

 
Answer #3    Answered By: Ora Hanson     Answered On: Oct 18

Thanks for your help. I can now copy array  b data in excel sheet
column 1.

How do I copy this excel 10000 cells data back to array c in a
single command.

 
Answer #4    Answered By: Angel Watkins     Answered On: Oct 18

I'm 95% sure you can't. Use a loop (it'll be a lot faster than writing
to a sheet from an array  with a loop).

 
Answer #5    Answered By: Burkett Bernard     Answered On: Oct 18

You can't copy it into a defined array  item, but if you say:

Dim vData as Variant
vData = ActiveSheet.Range(Cells(1, 1), Cells(10000, 1))

...VBA will create vData as an array like:

vData(1 to 10000, 1 to 1) as Variant

...and if you later say:

vData = ActiveSheet.Range(Cells(1, 1), Cells(10, 1))

...VBA will automatically redimension it as:

vData(1 to 10, 1 to 1) as Variant

 
Didn't find what you were looking for? Find more on Copying whole array in a column in single command Or get search suggestion and latest updates.




Tagged: