How do I reference tables in Excel using VBA?

by

The OP asked, is it possible to reference a table, not how to add a table. So the working equivalent of 

Sheets("Sheet1").Table("A_Table").Select

would be this statement:

Sheets("Sheet1").ListObjects("A_Table").Range.Select

or to select parts (like only the data in the table):

Dim LO As ListObject
Set LO = Sheets("Sheet1").ListObjects("A_Table")
LO.HeaderRowRange.Select        ' Select just header row
LO.DataBodyRange.Select         ' Select just data cells
LO.TotalsRowRange.Select        ' Select just totals row

For the parts, you may want to test for the existence of the header and totals rows before selecting them.

And seriously, this is the only question on referencing tables in VBA in SO? Tables in Excel make so much sense, but they’re so hard to work with in VBA!

More Related Contents:

  1. Update an Excel table with new values from another table
  2. MS Excel: insert a row automatically if the content in a cell is <> that in cell of the last row?
  3. vba excel: how to copy characters from a cell to another
  4. Microsoft Excel ActiveX Controls Disabled?
  5. What is the default scope of worksheets and cells and range?
  6. Is there any event that fires when keys are pressed when editing a cell?
  7. Convert an entire range to uppercase without looping through all the cells
  8. Does the VBA “And” operator evaluate the second argument when the first is false?
  9. Are there disadvantages in putting code into Userforms instead of modules?
  10. Split cell values into multiple rows and keep other data
  11. Difference between ‘on error goto 0’ and ‘on error goto -1’ — VBA
  12. Is VBA an OOP language, and does it support polymorphism?
  13. What are .NumberFormat Options In Excel VBA?
  14. Excel: How to check if a cell is empty with VBA? [duplicate]
  15. ShowAllData method of Worksheet class failed
  16. Assign keyboard shortcut to run procedure
  17. return csv file as recordset
  18. VBA workbooks.Close without being prompted to if the user wants to save?
  19. Extract Data from Word Document to an Excel SpreadSheet
  20. Excel macro -Split comma separated entries to new rows [duplicate]
  21. Get text from clipboard using GetText – avoid error on empty clipboard
  22. Copy a selected range to another worksheet
  23. Benchmarking VBA Code
  24. Excel macro – paste only non empty cells from one sheet to another
  25. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  26. Display milliseconds in Excel
  27. Error 1004 when setting Range.Locked
  28. How to extract groups of numbers from a string in vba
  29. Function Overloading and UDF in Excel VBA
  30. VBA Excel simple Error Handling

Leave a Comment