Excel Tables
Page 14
Adding Rows
There are a few ways to add new rows to a Table. If you're adding one row, use the ListRows object's Add method, which returns a ListRow object that can then be used to add values to that new row:
Dim Table As ListObject
Dim NewRow As ListRow
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
Set NewRow = Table.ListRows.Add
With NewRow.Range
.Columns(1).Value = #1/1/2015#
.Columns(2).Value = "Transaction 20"
.Columns(3).Value = "Expense"
.Columns(4).Value = -75
End With
Note that in this example, the Position parameter was not passed to the Add method, which resulted in the new row being appended to the end of the Table. To insert the new row at a specific position in the Table, use the Position parameter.
To append more than one row to the bottom of a Table, it's more efficient to add the rows in one step than to invoke the ListRows object's Add method multiple times. In the next example, the total row is disabled, the new data is copied into the empty cells immediately below the Table, and the Total row is enabled; if the TotalRow is not disabled, the Table does not recognize the new rows and therefore does not expand to include them. The new data is copied from the range "A2:D11" on the "Data" worksheet:
Dim Table As ListObject
Dim NewValues As Variant
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
NewValues = ThisWorkbook.Worksheets("Data").Range("A2:D11").Value
Table.ShowTotals = False
With Table.DataBodyRange
.Resize(10).Offset(.Rows.Count).Value = NewValues
End With
Table.ShowTotals = True
To insert multiple rows into the middle of a Table, you use the Range object's Insert method to insert empty cells, and then those cells are filled with the new row data. In the next example, 10 rows of data are inserted after the existing row 2 (and before row 3):
Dim Table As ListObject
Dim NewValues As Variant
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
NewValues = ThisWorkbook.Worksheets("Data").Range("A2:D11").Value
With Table.DataBodyRange
.Resize(10).Offset(2).Insert Shift:=xlShiftDown
.Resize(10).Offset(2).Value = NewValues
End With
Deleting Rows
The following sections show different methods for deleting rows from a Table by using VBA. The method you use depends on how many rows you want to delete.
Deleting One Row
You use the ListRow object's Delete method to delete a single row:
Dim Table As ListObject
Dim ListRow as ListRow
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
Set ListRow = Table.ListRows(3)
ListRow.Delete
In this example, the variable ListRow is assigned the third ListRow object in the ListRows collection, and then the ListRow object's Delete method is invoked. Here is an alternative, shorter version of the example that does not require the ListRow variable:
Dim Table As ListObject
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
Table.ListRows(3).Delete
Deleting Multiple Rows
Deleting multiple rows at once requires use of the Range object's Delete method. In the next example, 10 rows are deleted, starting at row 3:
Dim Table As ListObject
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
Table.DataBodyRange.Resize(10).Offset(2).Delete
Deleting All Rows
The next example deletes all the rows in a Table:
Dim Table As ListObject
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
Table.DataBodyRange.Delete
CAUTION
In this example, the DataBodyRange object is set to Nothing after the code completes. Any subsequent references to this object returns an error unless at least one row is added to the Table.
Looping
The following sections show methods for looping through rows and columns utilizing Table objects. You could also accomplish this by using row or column numbers, which is beyond the scope of this book.
Looping Through Rows
This example loops through all rows of the Table, adding each amount to the TotalExpenses variable for any row with "Expense" in the third column and printing the output to the Immediate window:
Dim Table As ListObject
Dim ListRow As ListRow
Dim TotalExpenses As Double
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
For Each ListRow In Table.ListRows
If ListRow.Range.Columns(3).Value = "Expense" Then
TotalExpenses = TotalExpenses + ListRow.Range.Columns(4).Value
End If
Next ListRow
Debug.Print "Total expenses: " & TotalExpenses
The following is an alternative method that uses the column names:
Dim Table As ListObject
Dim ListRow As ListRow
Dim TotalExpenses As Double
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
For Each ListRow In Table.ListRows
If Intersect(ListRow.Range, Table.ListColumns("Category").Range)_
.Value = "Expense" Then
TotalExpenses = TotalExpenses + Intersect(ListRow.Range, Table.ListColumns("Amount").Range).Value
End If
Next ListRow
Debug.Print "Total expenses: " & TotalExpenses
Looping Through Columns
This example loops through the columns of a Table, printing each column's name to the Immediate window by using the ListColumns collection and a For/Each statement:
Dim Table As ListObject
Dim ListColumn As ListColumn
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
For Each ListColumn In Table.ListColumns
Debug.Print ListColumn.Name
Next ListColumn
Filtering
One of the most powerful features of Tables is their ability to filter rows so that only the rows of interest are displayed. The Excel Object Model exposes the AutoFilter object (a child member of the ListObject object) and the AutoFilter method (a child member of the Range object), enabling complete control of the filtering process from VBA. You use the ListObject.AutoFilter object to inspect the current AutoFilter settings, refresh the AutoFilter, and clear the AutoFilter. You use the Range.AutoFilter method to set AutoFilter criteria.
Turning AutoFilter On and Off
You turn AutoFilter on and off by setting the ShowAutoFilter property to True (on) and False (off). The following example shows how to turn on the AutoFilter:
Dim Table As ListObject
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
Table.ShowAutoFilter = True
TIP
Since AutoFilter is an object that is set to Nothing when the AutoFilter is disabled, any code that references any of the AutoFilter object’s properties and methods will generate an error if the AutoFilter is disabled. To prevent errors, check that the AutoFilter is enabled and access the AutoFilter object’s properties and methods only if it is. The code examples in this section demonstrate this check.
NOTE
You can also enable and disable the AutoFilter by repeatedly invoking the Range object’s AutoFilter method without any parameters. But using this technique alone only toggles the AutoFilter state and, without checking another property, does not provide any control if a specific state is desired.
Determining the Filtering State
You use the AutoFilter object to determine whether the AutoFilter is enabled:
Dim Table As ListObject
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
If Table.ShowAutoFilter Then
Debug.Print "AutoFilter is on"
Else
Debug.Print "AutoFilter is off"
End If
If the AutoFilter is enabled, you use the AutoFilter object's FilterMode property to determine whether filtering criteria is in place:
Dim Table As ListObject
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
If Table.ShowAutoFilter Then
If Table.AutoFilter.FilterMode Then
Debug.Print "Filtering is active"
Else
Debug.Print "Filtering is inactive"
End If
Else
Debug.Print "AutoFilter is off"
End If
Determining Whether a Column Is Filtered
As shown in the following example, if the AutoFilter is enabled, you can use the Filter object's On property to determine whether a column has an active filter criteria. The Filter object is an item in the AutoFilter object's Filters property:
Dim Table As ListObject
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
If Table.ShowAutoFilter Then
If Table.AutoFilter.Filters(3).On Then
Debug.Print "Column 3 is being filtered"
End If
Else
Debug.Print "AutoFilter is off"
End If
Creating Filters
You create (apply) filters one column at a time. You use the Range object's AutoFilter method to add and remove filter criteria. (The Range object's AutoFilter method is described earlier in this chapter.) When an AutoFilter criterion is applied, the header row is automatically enabled. The following are some examples, each of which begins with these lines of code and assumes that the Table has no active filter criteria:
Dim Table As ListObject
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
This example shows only rows with "Expense" in the third column:
Table.Range.AutoFilter Field:=3, Criteria1:="Expense"
This example shows only rows with "Expense" or "Income" in the third column:
Table.Range.AutoFilter Field:=3, Criteria1:=Array("Expense", "Income"), Operator:=xlFilterValues
This example shows only rows with values in the third column that start with "Transaction":
Table.Range.AutoFilter Field:=2, Criteria1:="Transaction*"
This example shows only rows with values in the fourth column that are greater than zero:
Table.Range.AutoFilter Field:=4, Criteria1:=">0"
This example shows only rows with "Income" in the third column and values in the fourth column that are greater than 100:
Table.Range.AutoFilter Field:=3, Criteria1:="Income"
Table.Range.AutoFilter Field:=4, Criteria1:=">100"
Reapplying the Active Filter Criteria
The visibility of rows in a filtered Table can become out of sync with the filter criteria as data is changed and new rows added. You can correct this situation by reapplying the active filter criteria, as shown in the following example:
Dim Table As ListObject
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
If Table.ShowAutoFilter Then
Table.AutoFilter.ApplyFilter
End If
Clearing One Column's Filter
You can clear one column's filter by using the AutoFilter method and specifying only the Field parameter. The following example clears the third column's filter criteria:
Dim Table As ListObject
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
Table.Range.AutoFilter Field:=3
Clearing All Column Filters
You can clear the filter criteria for all columns in one step without disabling AutoFilter by invoking the ShowAllData method, as shown here:
Dim Table As ListObject
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
If Table.ShowAutoFilter Then
Table.AutoFilter.ShowAllData
End If
Hiding Drop-Down Controls by Column
You can hide AutoFilter drop-down controls in specific columns. The following example illustrates how to hide the AutoFilter drop-down control in the Table's second column:
Dim Table As ListObject
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
If Table.ShowAutoFilter Then
Table.Range.AutoFilter Field:=2, VisibleDropDown:=False
End If
Custom Routines
The following sections provide some custom routines to make your projects a little easier. More robust versions of these routines and a number of other routines plus useful utilities and code libraries are available at www.exceltables.com.
Doing a Bulk Insert
The following function inserts an array of values into a Table and returns the new rows as a range. If a row is specified, then the values are inserted above that row; otherwise, the values are appended to the bottom of the Table. The function also maps columns of values to columns in the Table by using the ColumnAssignments parameter. See the comments in the routine for more details on the parameters.
Public Function BulkInsertIntoTable( _
ByVal Table As ListObject, _
ByVal Values As Variant, _
Optional ByVal Position As Long = -1, _
Optional ByVal ColumnAssignments As Variant _
) As Range
' Insert an array of values into a Table. Optionally specify the row before
' which the new rows are inserted. Optionally specify how the columns are
' assigned. The new rows in the Table are returned as a Range.
'
' Syntax
'
' BulkInsertIntoTable(Table, Values, Position, ColumnAssignments)
'
' Table - A Table object.
'
' Values - A single value, a single dimension array of values, or a two
' dimension array of values.
'
' Position - The row number before which the new rows are inserted. Optional.
' If omitted then the new rows are appended to the end of the Table.
'
' ColumnAssignments - A single dimension array of integer values specifying
' which Table column receives what column of values in the Values parameter.
' Each element in the array is a column number in the Table. The position of
' the element in the array corresponds to the column in the Values array.
' Optional. If omitted then the values are placed in column order starting in
' the first Table column. For example, passing Array(2,3,1) results in this
' column mapping:
'
' Values column 1 is placed in Table column 2.
' Values column 2 is placed in Table column 3.
' Values column 3 is
placed in Table column 1.
Dim Calculation As XlCalculation
Dim ScreenUpdating As Boolean
Dim Result As Long
Dim TwoDimensionArray As Boolean
Dim WorkArray As Variant
Dim Column As Long
Dim SourceColumn As Long
Dim TargetColumn As Long
Dim ShowTotals As Boolean
Dim InsertRange As Range
' Exit if no values to insert
If IsEmpty(Values) Then Exit Function
Calculation = Application.Calculation
Application.Calculation = xlCalculationManual
ScreenUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False
' Normalize Values parameter - must be a two-dimension array
On Error Resume Next
Result = LBound(Values, 2)
TwoDimensionArray = Err.Number = 0
On Error GoTo 0
If Not TwoDimensionArray Then
If Not IsArray(Values) Then
Values = Array(Values)
End If
ReDim WorkArray(1 To 1, 1 To UBound(Values) - LBound(Values) + 1)
For Column = 1 To UBound(WorkArray, 2)
WorkArray(1, Column) = Values(Column - 1 + LBound(Values))
Next Column
Values = WorkArray
End If
' Normalize Position parameter
If Position < 0 Then
Position = Table.ListRows.Count
End If
Position = Application.Max(1, Application.Min(Position, Table.ListRows.Count + 1))
' Save total row setting and disable total
ShowTotals = Table.ShowTotals
Table.ShowTotals = False
' Insert the new rows
If Table.ListRows.Count > 0 And Position <= Table.ListRows.Count Then
Table.DataBodyRange.Resize(UBound(Values)).Offset(Position - 1).Insert Shift:=xlShiftDown
End If
If Table.ListRows.Count > 0 Then
Set InsertRange = Table.DataBodyRange.Resize(UBound(Values)).Offset(Position - 1)
Else
Set InsertRange = Table.InsertRowRange.Resize(UBound(Values))
End If
If IsEmpty(ColumnAssignments) Or IsMissing(ColumnAssignments) Then
InsertRange.Value = Values
Else