Results 1 to 3 of 3

How to turn Listobject into an Array

Threaded View

  1. #1
    Registered User
    Join Date
    09-25-2008
    Location
    Winnetka, CA
    Posts
    83

    How to turn Listobject into an Array

    My spreadsheets use a lot of ListObjects (tables). This excellent article explains that importing data into an array is much faster than pulling it from the spreadsheet:

    https://www.soa.org/News-and-Publica...s42-roper.aspx

    I know I can make the array by saying:

    Dim array1 as Variant 
    array1 = ActiveSheet.ListObjects(1).DataBodyRange

    I'm sure that this concept will help me a lot if I can just resolve these issues:

    1. One of the things I like about ListObjects is how I can refer to a column by name such as:

     tblInfo.ListColumns("FirstName")  ' This is better than a column number because I may change the columns around. 
    ' But if I really need the column number, I can use:
     tblInfo.ListColumns("FirstName").Index
    Is there a way to do that in an array?


    2. I can find a specific value in the table with something like this:

    
    ID = InputBox("Enter ID Number")
    
    WorkerName = WorksheetFunction.VLookup(ID, tblInfo.Range, 2, False)
    Is there a way to do that in an array?


    3. How much data can the array hold reasonably? I have some tables with 17,000 rows and about a dozen columns.
    Last edited by shawnvw; 09-06-2015 at 09:55 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Turn off calculations before query refresh....turn them back on after
    By mk3ll00 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-30-2014, 03:25 PM
  2. How to Turn repeated uneven blocks of rows into columns array
    By kimyuval in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-26-2013, 06:11 PM
  3. Auto calc on, then turn off, then runtime error how to turn back on
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-22-2011, 10:37 AM
  4. Replies: 2
    Last Post: 03-12-2011, 04:07 AM
  5. Replies: 1
    Last Post: 07-30-2010, 03:35 PM
  6. ListObject
    By costadina in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-24-2009, 08:20 PM
  7. Turn Text into an array function
    By gwiz in forum Excel General
    Replies: 3
    Last Post: 02-02-2008, 04:52 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1