12

I have a table in an excel sheet and I want to select the entire first row. Is there an easier/faster way to reference a table than the normal 

 Range("A2").End(xlDown).Select

method? Seems that by using a Table I should gain an easier access route to the data. Thanks.

Community
  • 1
  • 1
Ben A
  • 199
  • 2
  • 3
  • 13

2 Answers2

64

With these codes you can select different parts of a table.

Entire Table:
ActiveSheet.ListObjects("Table1").Range.Select

Table Header Row:
ActiveSheet.ListObjects("Table1").HeaderRowRange.Select

Table Data:
ActiveSheet.ListObjects("Table1").DataBodyRange.Select

Third Column:
ActiveSheet.ListObjects("Table1").ListColumns(3).Range.Select

Third Column (Data Only):
ActiveSheet.ListObjects("Table1").ListColumns(3).DataBodyRange.Select

Select Row 4 of Table Data:
ActiveSheet.ListObjects("Table1").ListRows(4).Range.Select

Select 3rd Heading:
ActiveSheet.ListObjects("Table1").HeaderRowRange(3).Select

Select Data point in Row 3, Column 2:
ActiveSheet.ListObjects("Table1").DataBodyRange(3, 2).Select

Subtotals:
ActiveSheet.ListObjects("Table1").TotalsRowRange.Select

For a full guide on tables see The VBA Guide To ListObject Excel Tables.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • My table header starts from `A3`. So. I did this `ActiveSheet.ListObjects("Table3").HeaderRowRange.DataBodyRange.Copy` . This gives error. How can select table starts from `A3`? – user9431057 Aug 13 '18 at 21:03
  • What do you exactly want to copy? The whole table or just the header? Both for both an example is given in this answer. – Pᴇʜ Aug 14 '18 at 06:00
  • I need the table with header. However, My table header starts from `A3`. So, when I copy table into a different sheet using `("Table 1").Range().copy` it takes `A1`, `A2` too, which I don't need. I tried `("Table1").HeaderRowRange.DataBodyRange.Copy` it does not work. What is the solution for that? – user9431057 Aug 14 '18 at 13:15
  • @user9431057 no `ActiveSheet.ListObjects("Table1").Range.Select` will only select eveything that belongs to the table nothing more. I recommend to ask a new question to that and show a screenshot in that question. This cannot be answered in a comment. – Pᴇʜ Aug 14 '18 at 13:18
  • yeah, that is a little tricky, I will post the question as soon as I can! – user9431057 Aug 14 '18 at 13:52
  • fyi from [here](https://www.dingbatdata.com/2017/11/24/referencing-listobjects-with-vba/): exclude sheet specification and get column by name: `Range("Table1").ListObject.ListColumns("ColumnName")` – sam-6174 Sep 28 '18 at 22:08
  • How do you select columns 3 to 5? – Diego Feb 11 '22 at 22:09
  • To select the data only of a column in your existing active cell, I use this: ActiveSheet.ListObjects("Table1").ListColumns(ActiveCell.Column).DataBodyRange.Select – Fandango68 Jan 11 '23 at 01:17
0

This is the shortest way I know:

Rows(1).Select

Here you can fin some example about it.

Nicolaesse
  • 2,554
  • 12
  • 46
  • 71
  • this doesn't select a row of a table but a row of the entire sheet. But we are talking about formatted tables in a sheet here. See [Overview of Excel tables](https://support.office.com/en-us/article/Overview-of-Excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c) – Pᴇʜ Apr 22 '17 at 15:53