One-dimensional array from Excel Range

by

I know you already accepted an answer but here is simpler code for you:

If you are grabbing a singe row (with multiple columns) then use:

Securities = application.transpose(application.transpose _
             (Worksheets(3).Range("A8:A" & SymbolCount).Value))

If you are grabbing a single column (with multiple rows) then use:

Securities = application.transpose(Worksheets(3).Range("A8:A" & SymbolCount).Value)

So, basically you just transpose twice for rows and once for columns.

Update:

Large tables might not work for this solution (as noted in the comment below):

I used this solution in a large table, and I found that there is a limitation to this trick: Application.Transpose(Range("D6:D65541").Value) ‘runs without error, but Application.Transpose(Range("D6:D65542").Value) ‘run-time error 13 Type mismatch

Update 2:

Another problem you might have as mentioned in the comments:

If one exceeds 255 characters, the function fails.

It has been a long time since I worked with Excel VBA but this might be a general limitation of accessing the data this way?

More Related Contents:

  1. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  2. Excel Number Separation [closed]
  3. excel vba compare & copy worksheets from 2 excel files
  4. VBA Stuck after certain loop
  5. How do I use FileSystemObject in VBA?
  6. Is the . in .Range necessary when defined by .Cells?
  7. How to return a result from a VBA function
  8. Cycle through sub-folders and files in a user-specified root directory [duplicate]
  9. Convert Text to Date?
  10. Properly Handling Errors in VBA (Excel)
  11. Password hash function for Excel VBA
  12. Is there a way to slow down a Web Scraper so it will pick up the code?
  13. Aggregate, Collate and Transpose rows into columns
  14. Excel vba to create every possible combination of a Range
  15. Excel VBA: getting row of clicked button [duplicate]
  16. VBA Many buttons point to the same _Click sub
  17. Excel VBA Filter Change event handler
  18. How to copy only values in excel vba from a range?
  19. How to suppress Update Links warning?
  20. How do we use restful APIs from Excel macros (vba)?
  21. Run a Macro from Another Workbook
  22. Cannot VBA write data to cells in Excel 2007/2010 within a function
  23. Handle “No cells were found” error when filtered range is empty
  24. Excel: the Incredible Shrinking and Expanding Controls [closed]
  25. How to send email to multiple recipients with addresses stored in Excel?
  26. Generate Word Documents (in Excel VBA) from a series of Document Templates
  27. Using Excel VBA Macro To Capture + Save Screenshot of Specific Area In Same File
  28. VBA: Modify chart data range
  29. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?
  30. How to save semi-colon delimited csv file using VBA?

Leave a Comment