Vba excel listobjects свойства и методы
In a table normally what we see is a data set but in VBA terminology there are much more such as there is range of the total data list range, the column is known as the list column and row is known as the list row and so on, so in order to access this properties we have an inbuilt function known as Listobjects and which is used with the worksheet function.
VBA ListObject is a way of referring to the excel tables while writing the VBA code. By using VBA LISTOBJECTS, we can create, delete table, and totally we can play around with excel tables in VBA code. Excel Tables are tricky, beginners, and even to an extent, intermediate level users find it difficult to work with tables. Since this article talks about referencing excel tables Excel Tables In excel, tables are a range with data in rows and columns, and they expand when new data is inserted in the range in any new row or column in the table. To use a table, click on the table and select the data range. read more in VBA coding, it is better you have good knowledge about tables in excel.
When the data is converted to tables, we no longer work with a range of cells. Rather, we need to work with table ranges, so in this article, we will show you how to work with excel tables to write VBA codes Write VBA Codes VBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task. read more efficiently.
Create Table Format Using ListObjects in Excel VBA
For example, look at the below excel data.
Using the VBA ListObject code, we will create a table format for this data.
- For this data first, we need to find what is the last used row & column, so define two variables to find this.
Code:
Code:
Code:
Code:
Now we need to use VBA “ListObject.Add” method to create a table, and below is the syntax of the same.
ListObject.Add (Source, XlListObjectHasHeaders, Destination, TableStyleName)
Source: This is nothing for which range of cells we are inserting the table. So we can supply two arguments here, i.e., “xlSrcRange” and “xlSrcExternal.”
XlListObjectHasHeaders: If the table inserting data has headers or not. If yes, we can provide “xlYes.” If not, we can provide “xlNo.”
Destination: This is nothing but our data range.
Table Style: If you want to apply any table style, we can provide styles.
- Ok, now in the active sheet, we are creating the table, so the below code would create a table for us.
Code:
Code:
Code:
Ok, let’s run the code and see the magic.
It has created the table to the mentioned data and given the table name as “EmpTable.”
Formatting Excel Tables with VBA ListObjects
Once the Excel table has been created, we can work with tables by using VBA ListObject collection.
Code:
Code:
Now the variable “MyTable” holds the reference for the table “EmpTable.”
For example, if we want to select the entire table, then we need to use the “Range” object, and under this, we need to use the “Select” method.
Code:
This would select the entire data table, including the heading.
- If you want to select only the contents of the table without headers, then we need to use “DataBodyRange.”
Code:
Like this, we can play around with tables.
- Below is the list of activity codes for your reference.
Code:
Like this, we can use the “ListObject” collection to play around with excel tables.
Things to Remember
- VBA ListObject is the collection of objects to reference excel tables.
- To access ListObject collection first, we need to specify what worksheet we are referring to is.
Recommended Articles
This has been a guide to VBA ListObject. Here we discuss how to use VBA ListObject.Add method to create a table in excel using examples downloadable excel sheet. You can learn more from the following VBA articles –
A blog focused primarily on Microsoft Excel, PowerPoint, & Word with articles aimed to take your data analysis and spreadsheet skills to the next level. Learn anything from creating dashboards to automating tasks with VBA code!
All About The Tables
For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA. I personally use data tables as a way to store user settings without having to modify any VBA code. You can see examples of this in my Exporter Template where I use tables to store worksheet names and email addresses. In this article, I wanted to bring all the common ways of referencing table data with VBA into one place. Hopefully, this will serve as a guide you can come back to again and again so you can easily and efficiently incorporate tables into your VBA macro coding. Enjoy!
VBA Quick Links
What Is A Table?
A Table is simply a structured range where you can refer to different sections that are automatically mapped out (such as the Header Row or the column below the header "Amount"). Tables are an amazing feature that Microsoft added into Excel because they not only structure your data, but they also expand with your data as it grows. And if there is one thing you should know about creating a spreadsheet, it would be that making it as DYNAMIC as possible is always a good thing!
You can quickly create a Table by highlighting a range (with proper headings) and using the keyboard shortcut Ctrl + t. You can also navigate to the Insert tab and select the Table button within the Tables group.
The Parts of A Table
Below are some images to help you visualize the different parts of a Table object through the lens of the VBA coding language.
How Do I Find Tables?
Tables can be a little tricky to find if you are completely new to them because they can blend in very well. There is a small little indicator at the bottom right cell of a Table range to indicate there is a table. As you can see in the image below, this indicator can be very simple to find, but also can be a hassle!
Another great way to find a table (and its name) is to go into the Name Manager. You can get to the name manager by navigating to the Formulas tab and clicking the Name Manager button inside the Defined Names group.
By using the Filter menu in the right-hand corner of the Name Manager, you can narrow down your name list to just the Tables within the Workbook. The Name Manager will show you exactly where the tables are within the spreadsheet and also what the Table names are.
Selecting Areas of a Table with VBA
Select | VBA Coding |
---|---|
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 |
Inserting Rows and Columns into the Table
Select | VBA Coding |
---|---|
Insert A New Column 4 | ActiveSheet.ListObjects("Table1").ListColumns.Add Position:=4 |
Insert Column at End of Table | ActiveSheet.ListObjects("Table1").ListColumns.Add |
Insert Row Above Row 5 | ActiveSheet.ListObjects("Table1").ListRows.Add (5) |
Add Row To Bottom of Table | ActiveSheet.ListObjects("Table1").ListRows.Add AlwaysInsert:= True |
Add Totals Row | ActiveSheet.ListObjects("Table1").ShowTotals = True |
Deleting Parts of a Table
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
'Remove 3rd Column
tbl.ListColumns(3).Delete
'Remove 4th DataBody Row
tbl.ListRows(4).Delete
'Remove 3rd through 5th DataBody Rows
tbl.Range.Rows("3:5").Delete
'Remove Totals Row
tbl.TotalsRowRange.Delete
Deleting The Data in a Table
Delete all data rows from a table (except the first row)
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
'Delete all table rows except first row
With tbl.DataBodyRange
If .Rows.Count > 1 Then
.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
End If
End With
'Clear out data from first table row
tbl.DataBodyRange.Rows(1).ClearContents
If you have formulas in your table, you may want to keep those intact. The following modification will just remove constant values from the remaining first row in the Table Object.
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
'Delete all table rows except first row
With tbl.DataBodyRange
If .Rows.Count > 1 Then
.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
End If
End With
'Clear out data from first table row (retaining formulas)
tbl.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
Loop Through Each table Column or Row
Dim tbl As ListObject
Dim x As Long
Set tbl = ActiveSheet.ListObjects("Table1")
'Loop Through Each Column in Table
For x = 1 To tbl.ListColumns.Count
tbl.ListColumns(x).Range.ColumnWidth = 8
Next x
'Loop Through Every Row in Table
For x = 1 To tbl.Range.Rows.Count
tbl.Range.Rows(x).RowHeight = 20
Next x
'Loop Through Each DataBody Row in Table
For x = 1 To tbl.ListRows.Count
tbl.ListRows(x).Range.RowHeight = 15
Next x
Looking Up Values Within a Table
If you are storing values inside a Table, there may be scenarios where you wish to look up or find a value. There are many different lookup scenarios one might have, but for simplicity, I will provide a generic example. The following code looks to find and ID string within a specific table’s first column and returns that ID’s table row number. Hopefully, you can use the logic within this example and apply it to your specific needs.
Dim tbl As ListObject
Dim FoundCell As Range
Dim LookupValue As String
'Lookup Value
LookupValue = "ID-123"
'Store Table Object to a variable
Set tbl = ActiveSheet.ListObjects("Table1")
'Attempt to find value in Table's first Column
On Error Resume Next
Set FoundCell = tbl.DataBodyRange.Columns(1).Find(LookupValue, LookAt:=xlWhole)
On Error GoTo 0
'Return Table Row number if value is found
If Not FoundCell Is Nothing Then
MsgBox "Found in table row: " & _
tbl.ListRows(FoundCell.Row - tbl.HeaderRowRange.Row).Index
Else
MsgBox "Value not found"
End If
Reading Table Data into an Array Variable
Pulling in data from tables is a great tactic to incorporate in your VBA coding. Tables are ideal because they:
Are always structured the same
Can be moved anywhere on the spreadsheet without affecting your code
Automatically adjust their range size
One example of using Tables as a data source in a macro is shown in one of my Code Vault snippets which allows you to filter your data based on the words in a specified table. There are tons of different ways you can use tables to store settings and preferences dynamically for your macros. The below code shows you how to load in data from a single column and a multi-column table.
Single Column Table
Dim myTable As ListObject
Dim myArray As Variant
Dim TempArray As Variant
Dim x As Long
'Set path for Table variable
Set myTable = ActiveSheet.ListObjects("Table1")
'Create Array List from Table
TempArray = myTable.DataBodyRange
'Convert from vertical to horizontal array list
myArray = Application.Transpose(TempArray)
'Loop through each item in the Table Array (displayed in Immediate Window [ctrl + g])
For x = LBound (myArray) To UBound (myArray)
Debug.Print myArray(x)
Next x
End Sub
Multiple Column Table
Dim myTable As ListObject
Dim myArray As Variant
Dim x As Long
'Set path for Table variable
Set myTable = ActiveSheet.ListObjects("Table1")
'Create Array List from Table
myArray = myTable.DataBodyRange
'Loop through each item in Third Column of Table (displayed in Immediate Window [ctrl + g])
For x = LBound (myArray) To UBound (myArray)
Debug.Print myArray(x, 3)
Next x
End Sub
Resizing a Table
If needed, you can resize a table's dimensions by declaring a new range area for the Excel table to shrink or expand. Below are a couple of examples showing how you can perform this sort of size adjustment.
(A special thanks to Peter Bartholomew for requesting this on LinkedIn)
Dim rng As Range
Dim tbl As ListObject
Clearing Out a Table
There may be instances where you want to remove all the body data from your table (ie reset it). A problem may arise if you only have one row of data in your table. Since a table by definition must have one header row and at least one data row, you will get an error message if your code tries to delete a DataBodyRange with no data in it. For this reason, we need to account for both scenarios.
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
'Delete Table's Body Data
If tbl.ListRows.Count >= 1 Then
tbl.DataBodyRange.Delete
End If
End Sub
Change All Table Column's Total Row Calculations
Dim tbl As ListObject
Dim CalcType As Integer
Dim x As Long
Set tbl = ActiveSheet.ListObjects("Table1")
'What calculation should the Totals Row Have?
CalcType = 1 'or: xlTotalsCalculationSum
'Loop Through All Table Columns
For x = 1 To tbl.ListColumns.Count
tbl.ListColumns(x).TotalsCalculation = CalcType
Next x
'___________________________________________
'Members of xlTotalsCalculation
'Enum Calculation
' 0 None
' 1 Sum
' 2 Average
' 3 Count
' 4 Count Numbers
' 5 Min
' 6 Max
' 7 Std Deviation
' 8 Var
' 9 Custom
'___________________________________________
Getting to the ActiveTable
There may be instances where you want to make a personal macro that formats your selected table in a certain way our adds certain calculation columns. Since the Excel developers didn't create an ActiveTable command in their VBA language, you have no straightforward way of manipulating a user-selected table. But with a little creativity, you can make your own ActiveTable ListObject variable and do whatever you want with the selected table!
Dim SelectedCell As Range
Dim TableName As String
Dim ActiveTable As ListObject
Set SelectedCell = ActiveCell
'Determine if ActiveCell is inside a Table
On Error GoTo NoTableSelected
TableName = SelectedCell.ListObject.Name
Set ActiveTable = ActiveSheet.ListObjects(TableName)
On Error GoTo 0
'Do something with your table variable (ie Add a row to the bottom of the ActiveTable)
ActiveTable.ListRows.Add AlwaysInsert:= True
Exit Sub
'Error Handling
NoTableSelected:
MsgBox "There is no Table currently selected!", vbCritical
Are You A Visual Learner? Download My Example Workbook
Screenshot from one of the tabs in the downloadable file
After many requests, I put together a fun little interactive workbook that will show you how a bunch of the code described in this article actually works on a spreadsheet. It also serves as a good reference that you can save to your computer so you don't have to keep googling about Excel Tables whenever something slips your mind.
As always, in order to download this article's example file, you will need to be a subscriber of my free tips newsletter. If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.
Already Subscribed? Click HERE to log-in to the "Example Files" section
Other Good VBA Table Resources & Code
Anything Else?
Did you come to this page trying to find out how to do something with VBA and Excel tables and it wasn't covered? If that is the case, let me know what you were looking for in the comment section below. If it makes sense to add it to this guide and will definitely add it to the content. I look forward to reading your thoughts and/or recommendations!
How Do I Modify This To Fit My Specific Needs?
Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it!
I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).
I wish you the best of luck and I hope this tutorial gets you heading in the right direction!
VBA ListObject Object in Excel. The ListObjects collection contains listobject objet. It represents a object on worksheet and most powerful feature. This object contains different methods and properties. In the following tutorial you learn methods, properties of listobject object. And also learn syntax of it.
VBA ListObject Object Methods & Properties
We have different methods and properties for ListObject Object in Excel VBA.
The methods are Delete, ExportToVisio,Publish, Refresh, Resize, Unlink, and Unlist in Excel VBA.
The properties are Active, AlternativeText, Application, AutoFilter, Comment, Creator, DataBodyRange, DisplayName, DisplayRightToLeft, HeaderRowRange, InsertRowRange, ListColumns, ListRows, Name, Parent, QueryTable, Range, SharePointURL, ShowAutoFilter, ShowAutoFilterDropDown, ShowHeaders, ShowTableStyleColumnStripes, ShowTableStyleFirstColumn, ShowTableStyleLastColumn, ShowTableStyleRowStripes, ShowTotals, Slicers, Sort, SourceType, Summary, TableObject, TableStyle, TotalsRowRange, and XmlMap.
Let us see complete details about methods & properties and.
VBA ListObject Object Methods in VBA
Here are the listobject object methods in VBA. Let us see syntax each method.
Delete
Deletes the ListObject object and clears data on the Worksheet cells.
ExportToVisio
Exports a ListObject object to Visio.
Publish
Publishes the ListObject object to a server that is running Microsoft SharePoint Foundation.
Where
Target is a required parameter. It contains array of strings variant type data.
LinkSource is a required parameter. It contains Boolean type data depending on the target.
Refresh
Retrieves the current data and schema for the list from the server that is running Microsoft SharePoint Foundation.
Resize
The Resize method allows a ListObject object to be resized over a new range. No cells are inserted or moved.
Unlink
Removes the link to a Microsoft SharePoint Foundation site from a list and returns Nothing.
Unlist
Removes the list functionality from a ListObject object. After use, the range of cells that made up the the list will be a regular range of data.
VBA ListObject object Properties in VBA
Let us see the the listobject object properties in VBA. Let us see syntax of each property.
Active
Checks the active cell is inside the range of the ListObject object. It returns a Boolean value indicating whether a ListObject object on a worksheet is active or not. It allows to read only.
AlternativeText
This property returns or sets the descriptive text string for the specified table. It allows to read or write.
Application
It is a property and returns an Application object that represents the Microsoft Excel application.
AutoFilter
It filters a table using the AutoFilter feature. It allows to read only.
Comment
This property returns or sets the comment associated with the list object. It is to read or write and string type data.
Creator
It returns a 32-bit integer. It represents the application in which this object was created. It allows to read only and Long type data.
DataBodyRange
It returns a Range object that represents the range of values, excluding the header row, in a table. It allows to read only.
DisplayName
This property returns or sets the display name for the specified ListObject object. It allows to read or write and string type data.
DisplayRightToLeft
True if the specified ListObject is displayed from right to left instead of from left to right. False if the object is displayed from left to right. It allows to read only and Boolean type data.
HeaderRowRange
It returns a Range object that represents the range of the header row for a list. It allows to read only and contains range object.
InsertRowRange
This property returns a Range object representing the Insert row for the specified ListObject object. It allows to read only and contains range object.
ListColumns
It returns a ListColumns collection that represents all the columns in a ListObject object. It allows to read only.
ListRows
It returns a ListRows collection that represents all the columns in a ListObject object. It allows to read only.
This property returns or sets a String value that represents the name of the ListObject object.
Parent
It returns the parent object for the specified object. It allows to read only.
QueryTable
This property returns the QueryTable object that provides a link for the ListObject object to the list server. It allows to read only.
Range
It returns a Range object that represents the range to which the specified list object in the list applies.
SharePointURL
Returns a String representing the URL of the SharePoint list for a given ListObject object. It allows to read only and string type data.
ShowAutoFilter
This property returns Boolean to indicate whether the AutoFilter will be displayed. It allows to read or write and conatins Boolean type data.
ShowAutoFilterDropDown
It returns True when the AutoFilter drop-down for the ListObject object is displayed. It allows to read or write and conatins Boolean type data.
ShowHeaders
It returns or sets if the header information should be displayed for the specified ListObject object. It allows to read or write and conatins Boolean type data.
ShowTableStyleColumnStripes
This property returns or sets if the Column Stripes table style is used for the specified ListObject object. It allows to read or write and conatins Boolean type data.
ShowTableStyleFirstColumn
It returns or sets if the first column is formatted for the specified ListObject object. It allows to read or write and conatins Boolean type data.
ShowTableStyleLastColumn
It returns or sets if the last column is displayed for the specified ListObject object. It allows to read or write and conatins Boolean type data.
ShowTableStyleRowStripes
This property returns or sets if the Row Stripes table style is used for the specified ListObject object. It allows to read or write and conatins Boolean type data.
ShowTotals
It gets or sets a Boolean to indicate whether the Total row is visible.It allows to read or write and conatins Boolean type data.
Slicers
It returns a list of the table slicers associated with a ListObject. It allows to read.
It gets or sets the sort column or columns and sort order for the ListObject collection.
SourceType
This property returns an XlListObjectSourceType value that represents the current source of the list.
Summary
It returns or sets the description associated with the alternative text string for the specified table. It allows to read or write.
TableObject
This property returns a TableObject object. It allows to read only.
TableStyle
It gets or sets the table style for the specified ListObject object. It allows to read or write and contains variant type data.
TotalsRowRange
This property returns a Range object representing the Total row, if any, from a specified ListObject object. It allows to read only.
XmlMap
It returns an XmlMap object that represents the schema map used for the specified table. It allows to read only.
Instructions to use InputBox Method example macro
You can follow step by step instructions to run above macro example VBA code.
- Open Visual Basic Editor(VBE) by clicking Alt +F11
- Go to code window by clicking F7
- Copy VBA macro code or procedure
- Paste above copied code in code window
- Run macro by clicking F5 or Run command
Other Related References
Click on the following links for related reference articles. It helps for your reference.
Leave a Comment Cancel Reply
You must be logged in to post a comment.
Project Management Templates
Professional PM Templates Pack
Recent Posts
VBA Functions by Category
VBA Programming
Categories
Build professional tools and automation application for your projects more easily by referrring our Ready to use VBA Codes and Applications.
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.
Download the example file
I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.
Download the file: 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.
Select the data within a table
The DataBodyRange excludes the header and totals sections of the table.
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.
Select an entire column
The macro below shows how to select a column by its position, or by its name.
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.
Select a specific column header
This macro shows how to select the column header cell of the 5th column.
Select a specific column within the totals section
This example demonstrates how to select the cell in the totals row of the 3rd column.
report this ad 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.
Select the header row
The following macro selects the header section of the table.
Select the totals row
To select the totals row of the table, use the following code.
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.
Convert a table back to a range
This macro will convert a table back to a standard range.
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.
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.
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.
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.
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.
Set the default table style
The following macro sets the default table style.
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.
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.
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.
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.
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.
Add rows to the bottom of a table
The next macro will add a row to the bottom of a table
Delete columns from a table
To delete a column, it is necessary to use either the column index number or the column header.
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.
Add total row to a table
The total row at the bottom of a table can be used for calculations.
Types of totals calculation
Table header visability
Table headers can be turned on or off. The following will hide the headers.
Remove auto filter
The auto filter can be hidden. Please note, the table header must be visible for this code to work.
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.
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:
Reference parts of a table using the range object
Within VBA, a table can be referenced as if it were a standard range object.
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.
Counting columns
The following macro will count the number of columns within the table.
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.
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.
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.
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.
Conclusion
Wow! That was a lot of code examples.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
Don’t forget:
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
Tables are one of Excel’s most useful and powerful features, in this tutorial, we will go over how to use VBA to create a table, add a simple sort to a table, filter a table and perform other table-related tasks.
Create a Table With VBA
The ListObjects.Add Method can add a table to a worksheet, based on a range in that worksheet. We have the range shown in ($A$1:$B$8) on a worksheet called Sheet1.
The following code will add a table, called Table1 to your worksheet, based on the range ($A$1:$B$8) using the default Table Style:
ActiveWorkbook . Sheets ( "Sheet1" ) . ListObjects . Add ( xlSrcRange , Range ( "$A$1:$B$8" ) , , xlYes ) . Name = _
Inserting a Column at the End of the Table with VBA
You can use the ListColumns.Add method in order to add a column to the end of your table. We have our table called Table1 shown below.
You can add a column to your table using the following code, which will always add a column to the end of the table:
Inserting a Row at the Bottom of the Table with VBA
You can use the ListRows.Add method to add a row to the bottom of your table. We have our table called Table1 shown below.
The following code will always add a row to the bottom of your table.
Adding a Simple Sort with VBA
You can sort a table with VBA. We have our table called Table1 shown below and we can use VBA to sort the Sales Column from lowest to highest.
The following code will sort the Sales column in ascending order.
Filter a Table With VBA
You can also filter an Excel table using VBA. We have our table called Table1 and we would like to filter the table so that only sales of greater than 1500 are shown.
We can use the Autofilter method, which has five optional parameters. Since we’d like to filter the Sales column which is the second column we set the Field to 2, and we use the xlAnd operator parameter, which is used for dates and numbers.
ActiveWorkbook . Sheets ( "Sheet1" ) . ListObjects ( "Table1" ) . Range . AutoFilter Field : = 2 , Criteria1 : = _
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Clear the Filter with the ShowAllData Method in VBA
You can access the ShowAllData Method of the Worksheet class in order to clear the filter. If it’s a table’s filter(s) that you want to clear, then you first have to select a cell in the table, which you can do in VBA.
The ShowAllData method will generate an error if one does not use conditional logic in order to check if there has been a filter applied in the worksheet. The following code shows you how to do this:
Читайте также: