Tables are one of the most powerful features of Excel. Controlling them using VBA provides a way to automate that power, which generates a double benefit 🙂
Excel likes to store data within tables. The basic structural rules, such as (a) headings must be unique (b) only one header row allowed, make tables compatible with more complex tools. For example, Power Query, Power Pivot, and SharePoint lists all use tables as either a source or an output. Therefore, it is clearly Microsoft’s intention that we use tables.
However, the biggest benefit to the everyday Excel user is much simpler; if we add new data to the bottom of a table, any formulas referencing the table will automatically expand to include the new data.
Whether you love tables as much as I do or not, this post will help you automate them with VBA.
Tables, as we know them today, first appeared in Excel 2007. This was a replacement for the Lists functionality found in Excel 2003. From a VBA perspective, the document object model (DOM) did not change with the upgraded functionality. So, while we use the term ‘tables’ in Excel, they are still referred to as ListObjects within VBA.
Table of Contents
- Structure of a table
- Referencing the parts of a table
- Select the entire table
- Select the data within a table
- Get a value from an individual cell within a table
- Select an entire column
- Select a column (data only)
- Select a specific column header
- Select a specific column within the totals section
- Select an entire row of data
- Select the header row
- Select the totals row
- Creating and converting tables
- Table styles
- Change the table style
- Get the table style name
- Apply a style to the first or last column
- Adding or removing stripes
- Set the default table style
- Looping through tables
- Adding & removing rows and columns
- Add columns into a table
- Add rows to the bottom of a table
- Delete columns from a table
- Delete rows from a table
- Add total row to a table
- Table header visability
- Remove auto filter
- Other range techniques
- Using the union operator
- Assign values from a variant array to a table row
- Reference parts of a table using the range object
- Counting rows and columns
- Useful table techniques
- Show the table data entry form
- Check if a table exists
- Find out if a table has been selected, if so which
- Conclusion
Download the example file: Join the free Insiders Program and gain access to the example file used for this post.
File name: 0009 VBA Tables and ListObjects.zip
Structure of a table
Before we get deep into any VBA code, it’s useful to understand how tables are structured.
Range & Data Body Range
The range is the whole area of the table.
The data body range only includes the rows of data, it excludes the header and totals.
Header and total rows
The header row range is the top row of the table containing the column headers.
The totals row range, if displayed, includes calculations at the bottom of the table.
List columns and list rows
The individual columns are known as list columns.
Each row is known as a list row.
The VBA code in this post details how to manage all these table objects.
Referencing the parts of a table
While you may be tempted to skip this section, I recommend you read it in full and work through the examples. Understanding Excel’s document object model is the key to reading and writing VBA code. Master this, and your ability to write your own VBA code will be much higher.
Many of the examples in this first section use the select method, this is to illustrate how to reference parts of the table. In reality, you would rarely use the select method.
Select the entire table
The following macro will select the whole table, including the totals and header rows.
Sub SelectTable() ActiveSheet.ListObjects("myTable").Range.Select End Sub
Select the data within a table
The DataBodyRange excludes the header and totals sections of the table.
Sub SelectTableData() ActiveSheet.ListObjects("myTable").DataBodyRange.Select End Sub
Get a value from an individual cell within a table
The following macro retrieves the table value from row 2, column 4, and displays it in a message box.
Sub GetValueFromTable() MsgBox ActiveSheet.ListObjects("myTable").DataBodyRange(2, 4).value End Sub
Select an entire column
The macro below shows how to select a column by its position, or by its name.
Sub SelectAnEntireColumn() 'Select column based on position ActiveSheet.ListObjects("myTable").ListColumns(2).Range.Select 'Select column based on name ActiveSheet.ListObjects("myTable").ListColumns("Category").Range.Select End Sub
Select a column (data only)
This is similar to the macro above, but it uses the DataBodyRange to only select the data; it excludes the headers and totals.
Sub SelectColumnData() 'Select column data based on position ActiveSheet.ListObjects("myTable").ListColumns(4).DataBodyRange.Select 'Select column data based on name ActiveSheet.ListObjects("myTable").ListColumns("Category").DataBodyRange.Select End Sub
Select a specific column header
This macro shows how to select the column header cell of the 5th column.
Sub SelectCellInHeader() ActiveSheet.ListObjects("myTable").HeaderRowRange(5).Select End Sub
Select a specific column within the totals section
This example demonstrates how to select the cell in the totals row of the 3rd column.
Sub SelectCellInTotal() ActiveSheet.ListObjects("myTable").TotalsRowRange(3).Select End Sub
Select an entire row of data
The macro below selects the 3rd row of data from the table.
NOTE – The header row is not included as a ListRow. Therefore, ListRows(3) is the 3rd row within the DataBodyRange, and not the 3rd row from the top of the table.
Sub SelectRowOfData() ActiveSheet.ListObjects("myTable").ListRows(3).Range.Select End Sub
Select the header row
The following macro selects the header section of the table.
Sub SelectHeaderSection() ActiveSheet.ListObjects("myTable").HeaderRowRange.Select End Sub
Select the totals row
To select the totals row of the table, use the following code.
Sub SelectTotalsSection() ActiveSheet.ListObjects("myTable").TotalsRowRange.Select End Sub
OK, now we know how to reference the parts of a table, it’s time to get into some more interesting examples.
Creating and converting tables
This section of macros focuses on creating and resizing tables.
Convert selection to a table
The macro below creates a table based on the currently selected region and names it as myTable. The range is referenced as Selection.CurrentRegion, but this can be substituted for any range object.
If you’re working along with the example file, this macro will trigger an error, as a table called myTable already exists in the workbook. A new table will still be created with a default name, but the VBA code will error at the renaming step.
Sub ConvertRangeToTable() tableName As String Dim tableRange As Range Set tableName = "myTable" Set tableRange = Selection.CurrentRegion ActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, _ Source:=tableRange, _ xlListObjectHasHeaders:=xlYes _ ).Name = tableName End Sub
Convert a table back to a range
This macro will convert a table back to a standard range.
Sub ConvertTableToRange() ActiveSheet.ListObjects("myTable").Unlist End Sub
NOTE – Unfortunately, when converting a table to a standard range, the table formatting is not removed. Therefore, the cells may still look like a table, even when they are not – that’s frustrating!!!
Resize the range of the table
To following macro resizes a table to cell A1 – J100.
Sub ResizeTableRange() ActiveSheet.ListObjects("myTable").Resize Range("$A$1:$J$100") End Sub
Table styles
There are many table formatting options, the most common of which are shown below.
Change the table style
Change the style of a table to an existing pre-defined style.
Sub ChangeTableStyle() ActiveSheet.ListObjects("myTable").TableStyle = "TableStyleLight15" End Sub
To apply different table styles, the easiest method is to use the macro recorder. The recorded VBA code will include the name of any styles you select.
Get the table style name
Use the following macro to get the name of the style already applied to a table.
Sub GetTableStyleName() MsgBox ActiveSheet.ListObjects("myTable").TableStyle End Sub
Apply a style to the first or last column
The first and last columns of a table can be formatted differently using the following macros.
Sub ColumnStyles() 'Apply special style to first column ActiveSheet.ListObjects("myTable").ShowTableStyleFirstColumn = True 'Apply special style to last column ActiveSheet.ListObjects("myTable").ShowTableStyleLastColumn = True End Sub
Adding or removing stripes
By default, tables have banded rows, but there are other options for this, such as removing row banding or adding column banding.
Sub ChangeStripes() 'Apply column stripes ActiveSheet.ListObjects("myTable").ShowTableStyleColumnStripes = True 'Remove row stripes ActiveSheet.ListObjects("myTable").ShowTableStyleRowStripes = False End Sub
Set the default table style
The following macro sets the default table style.
Sub SetDefaultTableStyle() 'Set default table style ActiveWorkbook.DefaultTableStyle = "TableStyleMedium2" End Sub
Looping through tables
The macros in this section loop through all the tables on the worksheet or workbook.
Loop through all tables on a worksheet
If we want to run a macro on every table of a worksheet, we must loop through the ListObjects collection.
Sub LoopThroughAllTablesWorksheet() 'Create variables to hold the worksheet and the table Dim ws As Worksheet Dim tbl As ListObject Set ws = ActiveSheet 'Loop through each table in worksheet For Each tbl In ws.ListObjects 'Do something to the Table.... Next tbl End Sub
In the code above, we have set the table to a variable, so we must refer to the table in the right way. In the section labeled ‘Do something to the table…, insert the action to be undertaken on each table, using tbl to reference the table.
For example, the following will change the table style of every table.
tbl.TableStyle = "TableStyleLight15"
Loop through all tables in a workbook
Rather than looping through a single worksheet, as shown above, the macro below loops through every table on every worksheet.
Sub LoopThroughAllTablesWorkbook() 'Create variables to hold the worksheet and the table Dim ws As Worksheet Dim tbl As ListObject 'Loop through each worksheet For Each ws In ActiveWorkbook.Worksheets 'Loop through each table in worksheet For Each tbl In ws.ListObjects 'Do something to the Table.... Next tbl Next ws End Sub
As noted in the section above, we must refer to the table using its variable. For example, the following will display the totals row for every table.
tbl.ShowTotals = True
Adding & removing rows and columns
The following macros add and remove rows, headers, and totals from a table.
Add columns into a table
The following macro adds a column to a table.
Sub AddColumnToTable() 'Add column at the end ActiveSheet.ListObjects("myTable").ListColumns.Add 'Add column at position 2 ActiveSheet.ListObjects("myTable").ListColumns.Add Position:=2 End Sub
Add rows to the bottom of a table
The next macro will add a row to the bottom of a table
Sub AddRowsToTable() 'Add row at bottom ActiveSheet.ListObjects("myTable").ListRows.Add 'Add row at the first row ActiveSheet.ListObjects("myTable").ListRows.Add Position:=1 End Sub
Delete columns from a table
To delete a column, it is necessary to use either the column index number or the column header.
Sub DeleteColumnsFromTable() 'Delete column 2 ActiveSheet.ListObjects("myTable").ListColumns(2).Delete 'Delete a column by name ActiveSheet.ListObjects("myTable").ListColumns("Feb").Delete End Sub
Delete rows from a table
In the table structure, rows do not have names, and therefore can only be deleted by referring to the row number.
Sub DeleteRowsFromTable() 'Delete row 2 ActiveSheet.ListObjects("myTable").ListRows(2).Delete 'Delete multiple rows ActiveSheet.ListObjects("myTable").Range.Rows("4:6").Delete End Sub
Add total row to a table
The total row at the bottom of a table can be used for calculations.
Sub AddTotalRowToTable() 'Display total row with value in last column ActiveSheet.ListObjects("myTable").ShowTotals = True 'Change the total for the "Total Column" to an average ActiveSheet.ListObjects("myTable").ListColumns("TotalColumn").TotalsCalculation = _ xlTotalsCalculationAverage 'Totals can be added by position, rather than name ActiveSheet.ListObjects("myTable").ListColumns(2).TotalsCalculation = _ xlTotalsCalculationAverage End Sub
Types of totals calculation
xlTotalsCalculationNone xlTotalsCalculationAverage xlTotalsCalculationCount xlTotalsCalculationCountNums xlTotalsCalculationMax xlTotalsCalculationMin xlTotalsCalculationSum xlTotalsCalculationStdDev xlTotalsCalculationVar
Table header visability
Table headers can be turned on or off. The following will hide the headers.
Sub ChangeTableHeader() ActiveSheet.ListObjects("myTable").ShowHeaders = False End Sub
Remove auto filter
The auto filter can be hidden. Please note, the table header must be visible for this code to work.
Sub RemoveAutoFilter() ActiveSheet.ListObjects("myTable").ShowAutoFilterDropDown = False End Sub
I have a separate post about controlling auto filter settings – check it out here. Most of that post applies to tables too.
Other range techniques
Other existing VBA techniques for managing ranges can also be applied to tables.
Using the union operator
To select multiple ranges, we can use VBA’s union operator. Here is an example, it will select rows 4, 1, and 3.
Sub SelectMultipleRangesUnionOperator() Union(ActiveSheet.ListObjects("myTable").ListRows(4).Range, _ ActiveSheet.ListObjects("myTable").ListRows(1).Range, _ ActiveSheet.ListObjects("myTable").ListRows(3).Range).Select End Sub
Assign values from a variant array to a table row
To assign values to an entire row from a variant array, use code similar to the following:
Sub AssignValueToTableFromArray() 'Assing values to array (for illustration) Dim myArray As Variant myArray = Range("A2:D2") 'Assign values in array to the table ActiveSheet.ListObjects("myTable").ListRows(2).Range.Value = myArray End Sub
Reference parts of a table using the range object
Within VBA, a table can be referenced as if it were a standard range object.
Sub SelectTablePartsAsRange() ActiveSheet.Range("myTable[Category]").Select End Sub
Counting rows and columns
Often, it is useful to count the number of rows or columns. This is a good method to reference rows or columns which have been added.
Counting rows
To count the number of rows within the table, use the following macro.
Sub CountNumberOfRows() Msgbox ActiveSheet.ListObjects("myTable").ListRows.Count End Sub
Counting columns
The following macro will count the number of columns within the table.
Sub CountNumberOfColumns() Msgbox ActiveSheet.ListObjects("myTable").ListColumns.Count End Sub
Useful table techniques
The following are some other useful VBA codes for controlling tables.
Show the table data entry form
If a table starts at cell A1, there is a simple data entry form that can be displayed.
Sub ShowDataEntryForm() 'Only works if Table starts at Cell A1 ActiveSheet.ShowDataForm End Sub
The following screenshot shows the data form for the example table.
Check if a table exists
The following macro checks if a table already exists within a workbook. Change the tblName variable to adapt this to your requirements.
Sub CheckIfTableExists() 'Create variables to hold the worksheet and the table Dim ws As Worksheet Dim tbl As ListObject Dim tblName As String Dim tblExists As Boolean tblName = "myTable" 'Loop through eac worksheet For Each ws In ActiveWorkbook.Worksheets 'Loop through each table in worksheet For Each tbl In ws.ListObjects If tbl.Name = tblName Then tblExists = True End If Next tbl Next ws If tblExists = True Then MsgBox "Table " & tblName & " exists." Else MsgBox "Table " & tblName & " does not exists." End If End Sub
Find out if a table has been selected, if so which
The following macros find the name of the selected table.
Method 1
As you will see in the comments Jon Peltier had an easy approach to this, which has now become my preferred approach.
Sub SimulateActiveTable() Dim ActiveTable As ListObject On Error Resume Next Set ActiveTable = ActiveCell.ListObject On Error GoTo 0 'Confirm if a cell is in a Table If ActiveTable Is Nothing Then MsgBox "Select table and try again" Else MsgBox "The active cell is in a Table called: " & ActiveTable.Name End If End Sub
Method 2
This option, which was my original method, loops through each table on the worksheet and checks if they intersect with the active cell.
Sub SimulateActiveTable_Method2() Dim ActiveTable As ListObject Dim tbl As ListObject 'Loop through each table, check if table intersects with active cell For Each tbl In ActiveSheet.ListObjects If Not Intersect(ActiveCell, tbl.Range) Is Nothing Then Set ActiveTable = tbl MsgBox "The active cell is in a Table called: " & ActiveTable.Name End If Next tbl 'If no intersection then no tabl selected If ActiveTable Is Nothing Then MsgBox "Select an Excel table and try again" End If End Sub
Conclusion
Wow! That was a lot of code examples.
There are over 30 VBA macros above, and even this does not cover everything, but hopefully covers 99% of your requirements. For your remaining requirements, you could try Microsoft’s VBA object reference library (https://docs.microsoft.com/en-us/office/vba/api/Excel.ListObject)
Discover how you can automate your work with our Excel courses and tools.
Excel Academy
The complete program for saving time by automating Excel.
Excel Automation Secrets
Discover the 7-step framework for automating Excel.
Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.
Nice article.
Here’s an alternative to SimulateActiveTable()
On Error Resume Next
Set ActiveTable = ActiveCell.ListObject
On Error GoTo 0
Thank Jon – that’s a much better method. Simple without the need for looping.
It really really a good one. It gave me solution to all my doubts. Thanks a lot. Explanation is so nice and simple, one can easily understand. Once again thanks
Thanks Vijay, I’m glad it helped 🙂
When inserting a new row to a table how would you populate that row with data at the same time?
Personally, yes I would. There seems little point adding a new row, then populating it later.
Hi,
This site is very helpful.
Sorry to say, Jeff’s question wasn’t SHOULD he populate the new row when he creates, but rather, HOW would you populate the new row with data at the time of its creation.
Thanks,
Hi,
Lets say you have a table starting at row 10, 4 columns wide.
on cell A7 i would like to place the MAX value from the table column1
and on cell A8 the MIN value from the same column.
And this for each column.
How can this be done?
following code generates an error 9
myMax = Application.WorksheetFunction.Max(ActiveSheet.ListObjects(“Tabel1[kolom1]”).Range)
To reference a column in a Table, you would need to use the following syntax:
myMax = Application.WorksheetFunction.Max(ActiveSheet.ListObjects(“Tabel1”).ListColumns(“kolom1”).Range)
This is shorter…
RANGE(“Tabel1[kolom1]”)
Awesome post!!! Thank you so much.
I have a question for you:
Do you know a way of applying a customized table style using vBA?
I have only been able to use the predefined ones.
This works:
objSheet.ListObjects(sFile).TableStyle = “TableStyleMedium2
But this does not:
objSheet.ListObjects(sFile).TableStyle = “My_Style_Table”
Thanks!!
I’ve tested this, and I can select a custom style.
The custom styles only exist in the workbook they are created in (they are not available in other workbooks), so it may be that the style you’ve created doesn’t exist in the workbooks you’re trying to apply them in.
Hi,
Great learning points here. My question is I’m using the a Table as source data for a Pivot Table but the range I’m getting does not include the Header Row which is required by the Pivot Table.
This is my code line that gets the source data:
SrcData = shQB.Name & “!” & Range(“tbQB”).Address(ReferenceStyle:=xlR1C1
As I said this does not include the Header Row in the return
If I apply your line in article above such as
SrcData = ActiveSheet.ListObjects(“tbQB”).Range then I get an error code 13
H
Any idea on how I can get this to work greatly appreciated
Using structured references for Tables, the range should be defined as follows:
Range("tbQB[[#Headers],[#Data]]")That should include the header row, but not the totals row.
For some of the ‘check’ type subroutines, I’d suggest changing the sub to a function.
Example
sub CheckTableExists()
becomes
Function CheckTableExists(tblname as string) as boolean
Instead of the MsgBox responses inside the sub, you’d set the return value to TRUE or FALSE as appropriate
I just want to leave a Like.
Espescially for the last topic “Find out is a table is selected”.
Thanks!
Hello,
I look for a way to DISABLE the “Autofill ” for table columns.
I’ve tryed the following actions but did not work:
1. “Application.AutoCorrect.AutoExpandListRange = False”
2. “Application.AutoCorrect.AutoFillFormulasInLists = False”
How can I get this working out?
I’ve tested the following, and it appears to be working:
This post explained Excel tables as implemented in VBA. I watched long boring Excel tutorials. I’ve learned a lot from them but didn’t get the information about manipulating tables in VBA.
The codes presented here are straight forward.
Thanks a lot!
Instead of selecting a specific row, how would you select a row based on an active cell? Using (ActiveCell.Row) in lieu of the row number?
Sub SelectRowOfData()
ActiveSheet.ListObjects(“myTable”).ListRows(??).Range.Select
End Sub
You could just use a simple calculation based on row numbers:
Dim SelectRow As Integer SelectRow = ActiveCell.Row - ActiveSheet.ListObjects("myTable").ListRows(1).Range.Row + 1 ActiveSheet.ListObjects("myTable").ListRows(SelectRow).Range.SelectHi friends how to find and replace in table body range
Very useful information all in one place! Thank you!
I have a table where once a task is completed and column S contains a “Y”, I would like to move that row to the bottom of the table. How can I do this?
Thanks
What an excellent post. Thank you.
I came looking for one thing and found a wealth of information on other things I can use. Having said that, what I was looking for isn’t here and I hope you can help.
I have a workbook with many many tables and recently some corruptions in the file began to occur. The excel error report “Repaired Records: External data range from /xl/queryTables/queryTable17.bin part (External data range)”.
The xml report didn’t have any additional info.
Some of the tables are refreshed by powerquery from csv files in the same folder.
I had already renamed all of my tables to Tbl_AMoreDescriptiveTableName and have no idea which one is Table17.
Because I’ve renamed the tables, the original table numbers are nowhere to be found (not that I can see in name manager etc.)
I was given the VB code (excerpt below) but it only lists the table name and sheet it’s located but not the original table number that the MS error message refers to.
Is there a way to determine the same table number as referenced by Excel’s error handling ?
variable tbl As ListObject
.Cells(lRow, “A”) = tbl.Name
.Cells(lRow, “B”) = tbl.Parent.Name (for the sheet it’s located in)
It’s difficult to advise, as your file is corrupted. So it’s not a standard scenario.
If your file is corrupted, then it may be that Table17 doesn’t exist, but within the XML file structure Excel believes it does. You could try (a) rebuilding the file (b) finding some software that repairs Excel files.
Very useful information!
It’s probably a subset of what you have included, but I have an Excel Table (ValidTags) that is just a header and a list of values, and I want to update the table with a 1D array of values (TagArray). Might be bigger or smaller.
How do I do that?
This post is very useful, I got a lot of things that I didn’t know before ..
Thank you very much.
I’m attempting to use these table references to copy table contents to a blank sheet in another file, and I get a subscript out of range.
Sheets(“SourceSheetWithTable”).ListObjects(“ExcelTableName”).Range.Copy Workbooks(“Destination.xlsx”).Worksheets(“Sheet1”).Range(“A1”)
Does it not like going from a table to a regular range? Does the range have to match the size of the table?
The “Subscript out of range” error is because one of the items you’ve referenced doesn’t exist. When I’ve tested your code it works fine, so it’s definitely something to do with the names in your environment.
(a) You should double-check the names of everything, as there is probably a typo in there.
(b) Are you running the code with the workbook containing the “SourceSheetWithTable” sheet as the active workbook
Thanks- here’s what I cobbled together from some other sources. Instead of using the Copy method, it’s assigning .value to .value. It doesn’t seem as elegant but it works! I added my questions as comments. Could you tell me if I’m understanding correctly?
‘load a Range variable with the listobject.Range
With wsCopy.ListObjects(1)
Set src = .Range ‘Union(.HeaderRowRange, .DataBodyRange)
End With
‘Does this resize the destination range to match the size of the src.Range and then assign it the src.Range.Value? It looks like .Rows.Count and .Columns.Count inside the Resize method refer to src variable due to being inside the With?
With src
wsDest.Range(“A1”).Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
Is there any way to sort a table using a custom list? I’m new to VBA and all this information is great stuff!
Try it with the Macro recorder. You can sort a custom list using the standard sort dialog box. Then look at the code and see which bit appears to be the correct part to achieve what you want.
Thanks for your efforts to teach us
could you plz advise if want to add row in a table with active cells
is highly appreciated
Fantastic documentation for working with tables. Thanks for the gift!.
Manuel
Thank Manuel – I’m glad you found it useful 🙂
Very helpful reference on listobjects!
Can you suggest a way to populate a 2-column listbox with two columns from an Excel table using structured references?
Great post – my issue occurs when I do a .ListObjects(table_name).Range.ClearContents
the execution of the line above deletes my table_name and the table associated with it.
.ListObjects(table_name).Range.Value = data_obj
this line also deletes my table_name and the table associated with it.
data_obj is a variant that is a 2d array. I am trying to use the same table, but the dimensions change when new data is requested
My table gets created in VBA using the following
.ListObjects.Add(xlSrcRange, data_rng, , xlYes, , “TableStyleMedium2”).Name = table_name
Why is the table disappearing. I have these table names linked to charts, and the deletion of the table is causing a bit of pain. Any help will be greatly appreciated
By using Range, you are referencing the entire table object. Have you tried using:
.ListObjects(table_name).DataBodyRange.ClearContents
That should just clear the data, but keep the Table on the worksheet.
I forgot to mention; I am using Excel365
Thanks so much for the quick response. Yes, I have tried clearing the DataBodyRange and the HeaderRowRange both of these calls maintain the table
The second line where I do the assignment
.ListObjects(table_name).Range.Value = data_obj
also deletes the table_name and the table associated with the name
Because the table dimensions change, I do a resize
.ListObjects(table_name).Resize data_rng
I get data_rng as follows – cellAddr is A1 – the start of the table header row
Set data_rng = .Range(cellAddr).Resize(row_cnt, col_cnt)
Am I required to set the HeaderRowRange and the DataBodyRange separately when I want to assign the data_obj to the Range.Value?
I was hoping I could just assign
.ListObjects(table_name).Range.Value = data_obj
otherwise I need to unwind the data_obj to pull out the headers and the data body.
Thanks again
Is there any way to set the entire range of the table without setting the HeaderRowRange and the DataBodyRange separately?
.ListObjects(table_name).Resize data_rng
.ListObjects(table_name).Range.Value = data_obj
The second line will delete the table_name and the table associated with it.
Thank you so very much for this summary. I found everything except how to reference a cell by column name and by row number. I wish to loop through the values in a column called “Color”.
colr = tbl2.DataBodyRange(x, “Color”).Value
better yet, I’d like to find the row in this table that has a match to a specific color.
Answered my own question with clues from above.
Loop Through all items in a table column:
for x = 1 to tbl.ListRows.Count
colr = tbl.ListColumns(“Color”).Range(x + 1).Value
‘use colr for something
next x
Hi Steven,
I would just make on change to your method so that you use the DataBodyRange rather than the Range (this means you can use x rather than x+1)
for x = 1 to tbl.ListRows.Count colr = tbl.ListColumns("Color").DataBodyRange(x).Value 'use colr for something next xhere’s is what i would use:
For Each icell In [table1[color]]
colr = icell.Value
Debug.Print colr
Next
hey steven,
here’s an alternate method that i use to loop through all table rows in a specific column:
but you have to watch because your x +1 will exceed the number of rows in the table
dim icell as range
For Each icell In [Table_name[column_name]]
‘do whatever
next
this was a very helpful article. i have since used the following to address parts of a table. it’s just my preference to use these since i create most of my tables on the fly in vba.
not an exhaustive list, just thought i’d post them in case anybody else wants to give them a try.
Select the data within a table
?[Table_Summary_1].address
Get a value from an individual cell within a table ?[Table_Summary_1].cells(2,4).value
Select an entire column, header and totals row included
?[Table_Summary_1[[#All],PO]].Select
Select a column (data only)
?[Table_Summary_1[customer]].Select
$A$6:$A$14
?[Table_Summary_1].columns(1).Select
$A$6:$A$14
Select a specific column header
?[Table_Summary_1].cells(0,4).value
PO
?[Table_Summary_1[PO]].rows(0).Select
Select a specific column within the totals section activesheet.ListObjects(“Table5”).ListColumns(“weight”).Total.Value
Select an entire row of data
?[Table_Summary_1].rows(2).Address
$A$7:$N$7
Select the header row
[Table_Summary_1].rows(0).select
setting column alignment
[Table_Summary_1[Customer]].HorizontalAlignment = xlCenter
Hi Gary – Great addition. I often forget about the [] shorthand to EVALUATE. Thanks for including this.
Thanks for your discussion of Tables, I learned several things. The thing I was looking for, however, is below.
An SQL SELECT statement can be run on an Excel Table, BUT you can’t use the table name directly. One way to run the SQL statement is to use a Range name. So, my question is, how can I assign a table’s address to a range name, in VBA?
Hi Mike,
The Table contains a Range and DataBodyRange property.
Range = Whole table
DataBodyRange = Data only, without the header
So to get the address of a Table you could use:
Dim tbl As ListObject Set tbl = Worksheets("Template").ListObjects("Template") MsgBox tbl.Range.Address MsgBox tbl.DataBodyRange.AddressHi
I really appreciate this information. I sure am learning.
I am wanting to find a value and replace the row with amended data.
I have an unique no. assisgned to each row as part of the table.
I see you have shown how to select a table row. I am unsure how to use this info in find and replace a table row with the amended row data.
Deeanna
Great article. Thanks.
To clear formatting after converting to range we can use ClearFormats..
Sub ConvertTableToRange()
Dim rng As Range
Set rng = ActiveSheet.ListObjects(“myTable2”).Range
ActiveSheet.ListObjects(“myTable2”).Unlist
rng.ClearFormats
End Sub
I have not gone through all comments above so someone may have suggested this.
Thanks Vinamra that’s a useful piece of code 🙂
one of the best article on excel VBA table
thanks a lot
You’re welcome 😀
This is a really useful guide/reference and covers all of the topics that I have previously found difficult to grasp. Great work!
Wow 🤩 it’s a great article! Thank you.
How can I copy a “table” from sheet1 then paste it in sheet2 but when I do it again and every time I want to paste it in sheet2 it should be pasted above the previous pasted table and so on.
Also, I need to print the time stamp on the left top of every table like this: “Printed on 19/07/2023 11:11”