Tables

Tip! - Use Tables to create dynamic ComboBox('s) and Drop Down list('s).



Using tables in formula's

=INDEX(Table1,MATCH([@[Pro'#]],Table1[Pro '#],0)+0,1)

Note the "@" sign before[Pro'#] after the Match. This means that the formula is in the same table. If it were not you would need to specify the table like Table1[Pro'#]. "Table" indicates the Table itself and the [ ] brackets indicate the Column in that Table.



Table Commands

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

Using Slicer with Tables

Note! Slicer will not work with data that is filtered. The data must be apart of a "table" or a "pivot table".
Note! You can hide columns in the table and the slicer will still work.


Here is a Macro to hide and unhide columns based on a ComboBox.

Sub hidcol()
Dim col As Integer
col = [List!B1] + 1
If Worksheets("List").Range("B1") = 27 Then GoTo Line1

Columns(col).EntireColumn.Hidden = True
GoTo Line2

Line1:
Columns("B:AA").EntireColumn.Hidden = True
Line2:
Worksheets("Home").Shapes("Drop Down 5").OLEFormat.Object.Value = 28
End Sub


---

Sub unhidcol()
Dim col As Integer
col = [List!b2] + 1

If Worksheets("List").Range("B2") = 27 Then GoTo Line1
Columns(col).EntireColumn.Hidden = False
GoTo Line2

Line1:
Columns("B:AA").EntireColumn.Hidden = False

Line2:
Worksheets("Home").Shapes("Drop Down 6").OLEFormat.Object.Value = 28
End Sub



Slicer ... This will Clearing the slicer filter.

Sub ClearSlicer()

ActiveWorkbook.SlicerCaches("Slicer_Name").ClearManualFilter
ActiveWorkbook.SlicerCaches("Slicer_CareGiver").ClearManualFilter
ActiveWorkbook.SlicerCaches("Slicer_Weeks").ClearManualFilter
ActiveWorkbook.SlicerCaches("Slicer_Month").ClearManualFilter
ActiveWorkbook.SlicerCaches("Slicer_Year").ClearManualFilter

End Sub



Clearing a Table Filters

Sub TableFilterClear()
'
' TableFilterClear Macro
'
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=3
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=4
End Sub



Table Filter ... Setting a specific filter in a Field/Column

ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=1, Criteria1:= _
"David Duncan"      'Specifies David Duncan in field/column 1

ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=2, Criteria1:= _
"39"     'Specifies 39 in field/Column 2


Deleting Parts Of A Table

Sub RemovePartsOfTable()

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

End Sub

Deleting The Entire Table

Delete all data rows from a table (except the first row)

Sub ResetTable()


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

End Sub



Resize a table

Dim wsO As Worksheet
Set wsO = Worksheets("Order")
wsO.ListObjects("Table2").Resize Range("A9:D10")




Dynamic Sum of a column in a Table

In this example the data is in Table1(CellsA1:D4) or we could name Table1 "ProjectInvoice"


	A	B	C	D
1	Item #	Description	Qty	Price
2	1	Socket	1	$10.00
3	2	Plug	1	$15.00
4
5			Total	$25.00



In Cell D5 we could write the formula using Table1 or by its name. Here are both versions.
Note! that the formula is not in the table because that would cause a circular reference error.

=Sum(Table1[Price])
=Sum(ProjectInvoice[Price])