If it’s not showing the user gets the option to show it at that time.

Address(0, 0) & NL End If Msg Box s Msg, vb Information, “INFO” End Sub[/sourcecode] This selects just the header range of the table, if it is showing.

NOTE: Adding a data body (Data Body Range) to a table is as simple as inserting a row.

If it’s not there the user gets the option to add it at that time. Select Else If Msg Box(“Data range has been deleted.

[sourcecode language=”vb”] Sub Select Data() ‘/// Set misc variables Set WS = This Workbook.

This is quite a useful trick when working with tables via VBA.

Change these variables for testing in your environment. When working with tables via VBA it’s important to know how to look at them, and thus where to go for information. Name & “‘ column headers:” & NL & s Msg Msg Box s Msg, vb OKOnly, “COLUMN HEADERS” End Sub[/sourcecode] This will not loop through the table, but give a straight count of columns, along with which/where the start/end columns are. Count & ” columns in ‘” & s Table Name & “‘.” s Msg = s Msg & DNL & “Starting on column ” & lo Test.

These are some examples of how to get dimension(s) information from your tables. [sourcecode language=”vb”] Sub Table_Count Columns() On Error Resume Next Set WS = This Workbook. List Objects(s Table Name) On Error Go To 0 If WS Is Nothing Or lo Test Is Nothing Then Msg Box “There was an error setting the variables.”, vb Information, “ERROR!

If you’re not up to speed with tables, take a look at these: When looking for information about tables, or adding/deleting data, it can be tricky. Count) & “)” Msg Box s Msg, vb Information, UCase(s Table Name) End Sub[/sourcecode] Again, no loop, just find out how many rows are in the table and where they are. Count & ” rows in ‘” & s Table Name & “‘.” s Msg = s Msg & DNL & “Starting on row ” & lo Test. Data Body Range Is Nothing Then Msg Box “The source table has no data stucture.”, vb Exclamation, “ERROR! Delete End If ‘/// Resize to source table size For i Step = 1 To i Row Cnt lo Set. Add i Step Next i Step ‘/////////////////////////////////////////////////////////////////////////// ‘/// At this point the table is restructured, and the formulas which were ‘/// there are re-populated ‘/////////////////////////////////////////////////////////////////////////// End Sub[/sourcecode] This utilizes two tables. Add 1 End If End If ‘/// Using standard table referencing objects s Msg = “Standard table referencing” & DNL If Not lo Set. Header Row Range Is Nothing Then s Msg = s Msg & “Header range: ” & lo Set. Address(0, 0) & NL Else s Msg = s Msg & “Header range: (none set)” & NL End If If Not lo Set.

Here are some pieces of information to keep in mind, which isn’t very obvious, but are cornerstones in working with tables in VBA: If you’ve worked with tables in VBA, you may have noticed that when deleting a table’s entire data body, you are still left with a single, blank row. [sourcecode language=”vb”] Sub Table_Count Rows() On Error Resume Next Set WS = This Workbook. List Objects(s Table Name) On Error Go To 0 If WS Is Nothing Or lo Test Is Nothing Then Msg Box “There was an error setting the variables.”, vb Information, “ERROR! ” Exit Sub End If ‘/// Get the number of rows in the table i Row Cnt = lo Set. Count ‘/// Delete destination table data If Not lo Set. The second table is only used for its row size and makes the first table the same size. Data Body Range Is Nothing Then s Msg = s Msg & “Data range: ” & lo Set. Totals Row Range Is Nothing Then s Msg = s Msg & “Totals range: ” & lo Set. Address(0, 0) & NL Else s Msg = s Msg & “Totals range: (none set)” End If ‘/// Using range referencing objects s Msg = s Msg & DNL & “Range referencing” & NL If Not lo Set.

Show Headers = True End If End If End Sub[/sourcecode] This selects just the data range of the table, if it is not deleted. [sourcecode language=”vb”] Sub Select Total Row() ‘/// Set misc variables Set WS = This Workbook.

Add 1 End If End If End Sub[/sourcecode] This selects just the totals row range of the table, if it is showing.

* Check the links at the bottom of the post for additional information on these topics. Worksheets(s Sheet Name) ‘/// Set two tables, one to set (destination) ‘/// and one to resize from (source) Set lo Set = WS. List Objects(s Table Name2) ‘/// Delete destination table data If Not lo Set. Data Body Range Is Nothing Then Msg Box “The source table has no data stucture.”, vb Exclamation, “ERROR! While I am very hesitant to code something like this, here is an example. While you can access the range object of the underlying worksheet, it gets tough. [sourcecode language=”vb”] Sub List Table Parts() ‘/// Set misc variables Set WS = This Workbook.