This blog post will be based on working with tables via VBA. They present some unique issues, which has to do with how their structured in the object model. Good or bad, it’s what we have. These are based on my trial and errors, so hopefully this will help somebody out there. That being said I consider this an intermediate level post.
If you’re not up to speed with tables, take a look at these:
http://excelandaccess.wordpress.com/2013/01/07/why-you-should-use-structured-references-in-tables/
When looking for information about tables, or adding/deleting data, it can be tricky. Here are some pieces of information to keep in mind, which isn’t very obvious, but are cornerstones in working with tables in VBA:
- Deleting the DataBodyRange (data portion of a table) will set it to Nothing *
- If table parts (i.e. header, totals row) aren’t showing, attempting access will result in an error
- Accessing the straight Range of a table includes all visible parts (i.e.
[#All])
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. This is because you must have at least one row of data for the table to still be “alive”. No data rows, no table. You can hide the header and totals row, but you can’t get rid of the body. This presents some interesting coding when working with tables, and can be problematic, aka headache-inducing.
* Check the links at the bottom of the post for additional information on these topics.
Here are some VBA examples for doing various things with tables in VBA. The file will be available (link at bottom of post) for download.
Common variables used throughout
[sourcecode language=”text”] Public WS As Worksheet
Public loSet As ListObject
Public loGet As ListObject
Public loTest As ListObject
Public rCols As Range
Public rRows As Range
Public rBody As Range
Public rData As Range
Public rHeader As Range
Public rStart As Range
Public iCol As Long
Public iRow As Long
Public iStep As Long
Public iLastRow As Long
Public iRowCnt As Long
Public iColCnt As Long
Public sMsg As String
Public Const sSheetName As String = “Sheet1”
Public Const sTableName As String = “Table1”
Public Const sTableName2 As String = “Table2”
Public Const NL As String = vbNewLine
Public Const DNL As String = vbNewLine & vbNewLine[/sourcecode]
As you can see I used some constants for testing in this specific workbook. Change these variables for testing in your environment. I tried to keep the naming convention simple.
Getting information from your tables
When working with tables via VBA it’s important to know how to look at them, and thus where to go for information. These are some examples of how to get dimension(s) information from your tables.
Listing columns
This will step through a table and list the column headers in a message box, as well as the Immediate window in the Visual Basic Editor (Ctrl + G to show it).
[sourcecode language=”vb”] Sub Table_ListColumns()
On Error Resume Next
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loTest = WS.ListObjects(sTableName)
On Error GoTo 0
If WS Is Nothing Or loTest Is Nothing Then
MsgBox “There was an error setting the variables.”, vbInformation, “ERROR!”
Exit Sub
End If
sMsg = vbNullString
For iStep = 1 To loTest.ListColumns.Count
Debug.Print loTest.ListColumns.Item(iStep).Name
sMsg = sMsg & NL & iStep & “) ” & loTest.ListColumns(iStep)
Next iStep
sMsg = “‘” & loTest.Name & “‘ column headers:” & NL & sMsg
MsgBox sMsg, vbOKOnly, “COLUMN HEADERS”
End Sub[/sourcecode]
Column count
This will not loop through the table, but give a straight count of columns, along with which/where the start/end columns are.
[sourcecode language=”vb”] Sub Table_CountColumns()
On Error Resume Next
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loTest = WS.ListObjects(sTableName)
On Error GoTo 0
If WS Is Nothing Or loTest Is Nothing Then
MsgBox “There was an error setting the variables.”, vbInformation, “ERROR!”
Exit Sub
End If
sMsg = “There are ” & loTest.ListColumns.Count & ” columns in ‘” & sTableName & “‘.”
sMsg = sMsg & DNL & “Starting on column ” & loTest.ListColumns(1).Range.Column & ” (” & loTest.ListColumns(1) & “)”
sMsg = sMsg & NL & “Ending on column ” & loTest.ListColumns(loTest.ListColumns.Count).Range.Column & ” (” & loTest.ListColumns(loTest.ListColumns.Count) & “)”
MsgBox sMsg, vbInformation, UCase(sTableName)
End Sub[/sourcecode]
Row count
Again, no loop, just find out how many rows are in the table and where they are.
[sourcecode language=”vb”] Sub Table_CountRows()
On Error Resume Next
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loTest = WS.ListObjects(sTableName)
On Error GoTo 0
If WS Is Nothing Or loTest Is Nothing Then
MsgBox “There was an error setting the variables.”, vbInformation, “ERROR!”
Exit Sub
End If
sMsg = “There are ” & loTest.ListRows.Count & ” rows in ‘” & sTableName & “‘.”
sMsg = sMsg & DNL & “Starting on row ” & loTest.ListRows(1).Range.Row
sMsg = sMsg & NL & “Ending on row ” & loTest.ListRows(loTest.ListRows.Count).Range.Row
MsgBox sMsg, vbInformation, UCase(sTableName)
End Sub[/sourcecode]
Resizing Tables
Resize table based on original
This will basically clear out the manual data and leave the formulas. It gets a row count and uses that to insert rows to its original size. Please note these focus on resizing rows programmatically, not columns, which can be trickier.
[sourcecode language=”vb”] Sub ResizeTableRows()
‘/// Set misc variables
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loSet = WS.ListObjects(sTableName)
‘/// Ensure the table has a data range
If loSet.DataBodyRange Is Nothing Then
MsgBox “The source table has no data stucture.”, vbExclamation, “ERROR!”
Exit Sub
End If
‘/// Get the number of rows in the table
iRowCnt = loSet.ListRows.Count
‘/// Delete destination table data
If Not loSet.DataBodyRange Is Nothing Then
loSet.DataBodyRange.Delete
End If
‘/// Resize to source table size
For iStep = 1 To iRowCnt
loSet.ListRows.Add iStep
Next iStep
‘///////////////////////////////////////////////////////////////////////////
‘/// At this point the table is restructured, and the formulas which were
‘/// there are re-populated
‘///////////////////////////////////////////////////////////////////////////
End Sub[/sourcecode]
Resize table based on another tables size (in rows)
This utilizes two tables. The second table is only used for its row size and makes the first table the same size. It deletes the DataBodyRange as well, so all manual data will be erased, leaving only formulas.
[sourcecode language=”vb”] Sub ResizeTableRowsBasedOnAnother()
‘/// Set misc variables
Set WS = ThisWorkbook.Worksheets(sSheetName)
‘/// Set two tables, one to set (destination)
‘/// and one to resize from (source)
Set loSet = WS.ListObjects(sTableName)
Set loGet = WS.ListObjects(sTableName2)
‘/// Delete destination table data
If Not loSet.DataBodyRange Is Nothing Then
loSet.DataBodyRange.Delete
End If
‘/// Ensure source table has data range
If loGet.DataBodyRange Is Nothing Then
MsgBox “The source table has no data stucture.”, vbExclamation, “ERROR!”
Exit Sub
End If
‘/// Resize to source table size
For iStep = 1 To loGet.ListRows.Count
loSet.ListRows.Add iStep
Next iStep
‘///////////////////////////////////////////////////////////////////////////
‘/// At this point the table is restructured, and the formulas which were
‘/// there are re-populated
‘///////////////////////////////////////////////////////////////////////////
End Sub[/sourcecode]
Resize table columns
This example will add a single column. While I am very hesitant to code something like this, here is an example. While it’s easier to add rows, adding columns present some unique problems, which include:
- Where should the columns be inserted?
- Without proper absolute structured formula referencing, this could throw off formulas
- Ideally no data should be to the right of the table, so an error won’t be thrown.
[sourcecode language=”vb”] Sub ResizeTableCols()
‘/// Set misc variables
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loSet = WS.ListObjects(sTableName)
‘/// Ensure the table has a data range
If loSet.DataBodyRange Is Nothing Then
MsgBox “The source table has no data stucture.”, vbExclamation, “ERROR!”
Exit Sub
End If
‘/// Get the number of columns in the table, add one
iColCnt = loSet.ListColumns.Count + 1
‘/// Delete destination table data
If Not loSet.DataBodyRange Is Nothing Then
loSet.DataBodyRange.Delete
End If
‘/// Resize to source table size
On Error Resume Next
For iStep = 1 To iColCnt
If loSet.ListColumns(iStep) Is Nothing Then
Err.Clear
loSet.ListColumns.Add
If Err.Number > 0 Then
‘something prevented the column from being added
Exit For
End If
End If
Next iStep
On Error GoTo 0
‘///////////////////////////////////////////////////////////////////////////
‘/// At this point the table is restructured, and the formulas which were
‘/// there are re-populated
‘///////////////////////////////////////////////////////////////////////////
End Sub[/sourcecode]
Select table parts
Table ranges
It’s always best to work with actual table range referernces. While you can access the range object of the underlying worksheet, it gets tough. This shows an example.
[sourcecode language=”vb”] Sub ListTableParts()
‘/// Set misc variables
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loSet = WS.ListObjects(sTableName)
If loSet.DataBodyRange Is Nothing Then
If MsgBox(“Data range has been deleted. Create now?”, vbYesNo, “ERROR!”) = vbYes Then
loSet.ListRows.Add 1
End If
End If
‘/// Using standard table referencing objects
sMsg = “Standard table referencing” & DNL
If Not loSet.DataBodyRange Is Nothing Then
sMsg = sMsg & “Data range: ” & loSet.DataBodyRange.Address(0, 0) & NL
End If
If Not loSet.HeaderRowRange Is Nothing Then
sMsg = sMsg & “Header range: ” & loSet.HeaderRowRange.Address(0, 0) & NL
Else
sMsg = sMsg & “Header range: (none set)” & NL
End If
If Not loSet.TotalsRowRange Is Nothing Then
sMsg = sMsg & “Totals range: ” & loSet.TotalsRowRange.Address(0, 0) & NL
Else
sMsg = sMsg & “Totals range: (none set)”
End If
‘/// Using range referencing objects
sMsg = sMsg & DNL & “Range referencing” & NL
If Not loSet.DataBodyRange Is Nothing Then
sMsg = sMsg & “Data range: ” & loSet.Range(1, 1).Address(0, 0) & “:” & loSet.Range(loSet.Range.Rows.Count, loSet.Range.Columns.Count).Address(0, 0) & NL
End If
MsgBox sMsg, vbInformation, “INFO”
End Sub[/sourcecode]
Select table Header
This selects just the header range of the table, if it is showing. If it’s not showing the user gets the option to show it at that time.
[sourcecode language=”vb”] Sub SelectHeader()
‘/// Set misc variables
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loSet = WS.ListObjects(sTableName)
If Not loSet.HeaderRowRange Is Nothing Then
loSet.HeaderRowRange.Select
Else
If MsgBox(“Header row isn’t showing. Show now?”, vbYesNo, “ERROR!”) = vbYes Then
loSet.ShowHeaders = True
End If
End If
End Sub[/sourcecode]
Select table data
This selects just the data range of the table, if it is not deleted. If it’s not there the user gets the option to add it at that time.
NOTE: Adding a data body (DataBodyRange) to a table is as simple as inserting a row. This is quite a useful trick when working with tables via VBA.
[sourcecode language=”vb”] Sub SelectData()
‘/// Set misc variables
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loSet = WS.ListObjects(sTableName)
If Not loSet.DataBodyRange Is Nothing Then
loSet.DataBodyRange.Select
Else
If MsgBox(“Data range has been deleted. Create now?”, vbYesNo, “ERROR!”) = vbYes Then
loSet.ListRows.Add 1
End If
End If
End Sub[/sourcecode]
Select table Totals row
This selects just the totals row range of the table, if it is showing. If it’s not showing the user gets the option to show it at that time.
[sourcecode language=”vb”] Sub SelectTotalRow()
‘/// Set misc variables
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loSet = WS.ListObjects(sTableName)
If Not loSet.TotalsRowRange Is Nothing Then
loSet.TotalsRowRange.Select
Else
If MsgBox(“Totals row isn’t showing. Show now?”, vbYesNo, “ERROR!”) = vbYes Then
loSet.ShowTotals = True
End If
End If
End Sub[/sourcecode]
Show table parts
This routine has a Boolean (True/False) variable passed to it to turn on or off all table parts (non-formatting)
[sourcecode language=”vb”] Sub ShowTableParts(Optional ByVal bState As Boolean = True)
‘/// Set misc variables
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loSet = WS.ListObjects(sTableName)
loSet.ShowTotals = bState
loSet.ShowHeaders = bState
If bState = True Then loSet.ShowAutoFilter = bState
End Sub [/sourcecode]
Note that the ShowAutoFilter portion only runs if a True variable is passed (turning properties on). This is because if you turn off the header row, there’s nothing to filter on, so there’s no need to worry about it.
Summary
The important parts of the object model to look at with respect to tables are:
- ListObject
- DataBodyRange
- HeaderRowRange
- TotalsRowRange
- ListRows
- ListColumns
There are other things you can do with tables via VBA, this certainly isn’t the complete guide. Some of those things include:
- Work with slicers *
- Unlist it (convert to standard range)
- Set style/formatting options
Additional information
Jan Karel Pieterse: http://www.jkp-ads.com/articles/Excel2007Tables.asp
Colin Legg: http://colinlegg.wordpress.com/2012/09/01/clearing-excel-tables/
Sample file for download: tblExample
File tested and works in Excel 2010 and 2013 without issue.
A special thanks to Chris “Smitty” Smith, Christopher T. Fennell and Colin Legg, equally for the direction, support and inspiration for this blog post. Thank you.
Reblogged this on Julianrazif's Weblog.
This post was very useful to me, thank you!
Hi,
Can you please demonstrate how to merge 2 tables (in 2 separate worksheets) into a new table (in a new worksheet)?
table 1 has columns: “Name”, “Birth Date”
table 2 has columns: “Name”, “Age”
I want all 3 columns in the new (third) worksheet: “Name”, “Birth Date”, “Age”
Thanks in advance.
Best Regards,
Meni Porat
You would need to detail a little more information, like worksheet names, table names, version of Excel, etc. What would you do if there are duplicates? Are each table unique as-is? I would want more information. ASSUMING there was unique records in each table, I would add a 3rd column to table 1, title it “Age”, use the following formula..
=IFERROR(INDEX(table1,MATCH([@Name],table2[Name],0),2),””)
At that point I would copy/paste values as static in that column, double check values came over and get rid of table 2.
But again, this is all based on assumption. 😉
Have a great day!
Hi ExcelAndAccess,
Thank you for your prompt response.
Perhaps there’s some kind of misunderstanding….
Since this discussion deals with manipulating Excel tables using VBA, I expected your solution to be in VBA.
Now, about the information you wanted me to supply:
1) Your assumption is correct: both tables have unique records.
2) The worksheets names aren’t important: If your solution specifies “Sheet1” and “Sheet2” I’ll be able to adapt it to my worksheets’ names.
3) Let’s assume that each worksheet has only one table. Its name is also not vital, since one can write, for example:
Set loTest = WS.ListObjects(1)
4) Excel’s version is also superfluous, since tables didn’t exist before Excel’s 2007 version.
Best Regards,
Meni Porat
Well you should state your specifics if you want a specific answer. 😉
Assuming you want to merge both tables (on sheets and tables as stated) onto a new worksheet and a new table (again, assumed), you could use something like this…
[sourcecode language=”vb”]
Option Explicit
Sub MergeTables()
‘declare variables
Dim wsDEST As Worksheet
Dim loONE As ListObject
Dim loTWO As ListObject
Dim loDEST As ListObject
Dim loColHelper As ListColumn
Dim aNames() As Variant
Dim iStep As Long
Dim iFind As Long
‘set tables and destination sheet variables
Set loONE = ThisWorkbook.Worksheets(1).ListObjects(1)
Set loTWO = ThisWorkbook.Worksheets(2).ListObjects(1)
Set wsDEST = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
‘add helper column
On Error GoTo CannotAddColumn
Set loColHelper = loONE.ListColumns.Add(loONE.ListColumns.Count + 1)
loColHelper.DataBodyRange.NumberFormat = "General"
loColHelper.DataBodyRange.Formula = "=IFERROR(INDEX(Table2,MATCH([@Name],Table2[Name],0),2),0)"
loColHelper.DataBodyRange.Value = loColHelper.DataBodyRange.Value
On Error GoTo 0
‘copy table one, adjust header
loONE.Range.Copy wsDEST.Range("A1")
loColHelper.Delete
Set loDEST = wsDEST.ListObjects(1)
loDEST.HeaderRowRange(1, 3).Value = "Age"
‘loop through table two, see if anything didn’t make it over
aNames = loTWO.ListColumns("Name").DataBodyRange.Value
For iStep = LBound(aNames) To UBound(aNames)
iFind = 0
On Error Resume Next
iFind = WorksheetFunction.Match(aNames(iStep, 1), loDEST.ListColumns("Name").DataBodyRange, 0)
On Error GoTo 0
‘if found, return results
If iFind = 0 Then
loDEST.ListRows.Add loDEST.ListRows.Count + 1
loDEST.DataBodyRange(loDEST.ListRows.Count, 1).Value = aNames(iStep, 1)
loDEST.DataBodyRange(loDEST.ListRows.Count, 3).Value = Evaluate(WorksheetFunction.Match(aNames(iStep, 1), loTWO.ListColumns("Name").DataBodyRange, 0))
End If
Next iStep
‘indicate success
MsgBox "Tables merged successfully!", vbExclamation, "COMPLETE!"
Exit Sub
‘if there was a problem adding the helper column…
CannotAddColumn:
MsgBox "There was a problem adding a helper column to ‘" & loONE.Name & "’ on " & loONE.Parent.Name & ".", vbCritical, "ERROR!"
End Sub
[/sourcecode]
HTH
To note, the above code uses a helper column because it just copies the first table, then finds values in the second which didn’t exist in the first. There are many ways you could do this, including not using a helper column. I realize that may work in some cases, not in others. If you need something else, just let me know. 🙂
ExcelAndAccess
Hi ExcelAndAccess,
You are absolutely right: There are many solutions to this problem . The simplest, most elegant method would be with a pivot table (multiple consolidation ranges, using the good, old 2003 PivotTable wizard).
However, what I had in mind is a VBA solution which incorporates either the QueryTable or the ADO feature, with/without the ListObject.
I’d appreciate it very much if you could help me with either of these methods.
Best Regards,
Meni Porat
I’m assuming you meant using a PivotTable *after* consolidating the data sources? While PivotTable’s can be used for many things, two tables with unique values in each, I wouldn’t say a PivotTable is the way to go with that, but rather my first suggestion to use a formula to bring values over, filter, copy/paste. That would be the quickest.
The VBA method may not be my first choice, but it’s what you asked for. I would not use a QueryTable here, I’m not sure why that would even be an entertained thought. The data is already in a table and we can use the ListObject. Less overhead and faster results.
Regarding ADO, what would be the purpose? Reference an engine you don’t need? Again, not needed. As far as working with tables, if you are working in VBA, I’d always recommend using the ListObject (object).
If this is part of something larger or perhaps involving a database, that would indeed change things. Maybe the two tables you’re talking about are from a database? In any case, with what we have to go by, I would not recommend either of the methods you mentioned (of course unless there is other information I don’t know about).
1) I don’t really understand what you mean when you refer to consolidating data sources before using a PivotTable: the “multiple consolidation ranges” is an option in Step 1 of “Pivot Table and Pivot Chart” (I can’t attach a picture…).
2) I still think that using the Pivot Table wizard is the simplest, most elegant method for the problem.
The method you suggested is too cumbersome: writing a formula, adding a new column, cutting an pasting, deleting table 2….
3) The use of ADO/Query Table is just to add another solution to the basket… I agree that this is neither efficient nor desired.
Best Regards,
Meni Porat
Hi Meni,
With the current version being 2013, I haven’t used 2003 in quite some time, but I do remember the wizard. 😉
If writing one formula and copy/pasting is too “cumbersome”, I I’m not sure there’s anything I can do for ya. 🙂
I understand about bringing other solutions to the table. I would really only use ADO if I had to do some sort of query across data sets (i.e. crosstab query in Excel) or extract data from a database. While I certainly agree they’re great methods, I just don’t think in this case.
Have a great day!
ExcelAndAccess
“misunderstanding all you see” (Beatles: “Strawberry Fields Forever”)
Here’s a trick I learned from Dennis Taylor: How to incorporate the good, old 2003 PivotTable and PivotChart Wizard into… Excel 2007, Excel 2010 (and maybe even into Excel 2013 – I don’t know, since I don’t own this version…)
1. Open the “Customize the Quick Access Toolbar” (the down-pointing arrow next to the Quick Access Toolbar (QAT))
2. Select: “More Commands…”
3. On the left pane: select “choose commands from “commands not in the Ribbon”
4. Scroll down until you find the “PivotTable and PivotChart Wizard”
5. Click the “add” button between the left and the right panes of the screen. The new Wizard icon will be added to your QAT menu (on the right pane).
6. Click OK to confirm
7. Now that the new (old?) Wizard appears on your QAT, press it
8. In Step 1,
Choose the : “Multiple consolidation ranges” radio button and the “Pivot Table” radio button and press: “Next>”
9. In step 2,
Choose “create a single page field for me” and press: “Next>”
10. In step2b,
a. Select the first table’s data range (in the first worksheet), and click “add”
b. Select the second table’s data range (in the second worksheet) and click “add”
c. Add as many tables you want to consolidate. In my case, I have only 2 tables in two separate worksheets.
d. Press the “Next>” button to go the next step
11. In step 3,
You decide whether you want your pivot table in an existing worksheet or in a new one. My choice was: “New worksheet”.Click “Finish”
12. Lo and behold, a new Pivot Table consolidating the data from both tables was born…
Best Regards,
Meni Porat
True that Meni, most functionality is still there under the hood. Thanks for posting that, I’m sure it will be useful for many!
If you get a chance to check out Excel 2013, take a peek at the Relationships feature (blogged by Frederic Le Guen here: http://www.excel2013.info/relationship-between-tables/). While it’s not the same idea as what you’re talking about here it’s somewhat similar.
Happy Excelling!
ExcelAndAccess
Great article!
[…] ListObject Object Model ListObjects Object Model Working with Tables in Excel 2013, 2010 and 2007 (VBA) Working with Microsoft Excel tables in VBA […]
Good article – thanks.
If the table is filtered – is it possible to select only the filtered values?
As “.DataBodyRange.Value” on a filtered table selects all the data rather than the filtered values.
Yes Christopher, you can select the visible cells. This is an interesting point actually, because you don’t need to select anything, so I’m guessing you’re trying to do something else with this visible range. Below are two ways to select the visible cells
With a keyboard shortcut
The keyboard shortcut for selecting the visible cells (assuming you have the data body range selected first) is ALT+;
With VBA
[sourcecode language=”vb”]On Error Resume Next
Table.DataBodyRange.SpecialCells(xlCellTypeVisible).Select
On Error Goto 0[/sourcecode]
You have to use error trapping with the VBA because SpecialCells will fail if there are none of that recognized type available. This goes for any of the SpecialCells types. Because I use them often, generally I’m using some kind of logic like this:
[sourcecode language=”vb”]Dim MyRange As Range
Set MyRange = Nothing
On Error Resume Next
Set MyRange = Table.DataBodyRange.SpecialCells(xlCellTypeVisible) ‘or whatever you want here
On Error Goto 0
If Not MyRange Is Nothing Then
‘It’s a valid range, work with it here
Else
‘It’s NOT a valid range
End If[/sourcecode]
thanks – much appreciated – I had forgotten the SpecialCells() method.
Also thanks for the trapping logic.
And you guessed correctly. (And it’s been a while since I’ve done much vba)
cheers
Very good tutorial,
Can you help me? How do i get the row index in a table? How do i get a cell value in a table by the row index and a column name?
Thank you very much.
Not sure I follow. By a “row index,” do you mean to return a 1 for the tables first row, then increment down by 1 for every row below? If so, you can use something like…
=ROW()-ROW(Table1[[#Headers],[HeaderName]])
Where “Table1” is the name of your table and “HeaderName” is the name of the column header. It can be any column header name actually.
To specify a row number and a column name, you can index a table and return the intersection with this…
=INDEX(Table1,A1,MATCH(A2,Table1[#Headers],0))
Where “Table1″ is the name of your table, A1 houses the row number to return, and A2 houses the name of the column to look in.
If you wanted to do this with VBA, it could be used in a function like this…
[sourcecode language=”vb”]Function TableCellData(ByVal Table As ListObject, _
Optional ByVal TableRow As Long = 1, _
Optional ByVal TableColumn As Long = 1) As Variant
TableCellData = CVErr(xlErrNA)
On Error Resume Next
TableCellData = Table.DataBodyRange(TableRow, TableColumn)
On Error GoTo 0
End Function[/sourcecode]
Which you could call in an example like this…
[sourcecode language=”vb”]Sub TestFunction()
Dim TestTable As ListObject
Set TestTable = ActiveSheet.ListObjects(1)
MsgBox "(1, 1): " & TableCellData(TestTable) & vbNewLine & "(5, 2): " & TableCellData(TestTable, 5, 2)
End Sub[/sourcecode]
HTH
How do I convert a range of data records in a worksheet into a table using vba?
Regards
Lydia
Hi Lydia,
Here is some sample code which does that. It assumes you already know the full range of the source data, as well as what you want to name the table.
[sourcecode language=”vb”]Sub MakeTable()
Dim SourceSheet As Worksheet
Dim Table As ListObject
Dim SourceRange As Range
Set SourceSheet = ActiveSheet
Set SourceRange = SourceSheet.Range("A1:C5")
Set Table = SourceSheet.ListObjects.Add( _
SourceType:=xlSrcRange, _
Source:=SourceRange, _
XlListobjectHasHeaders:=xlYes)
Table.DisplayName = "YourTableName"
End Sub[/sourcecode]
HTH
HI excelandaccess
Very interesting your post
I had a problem add columns and formulas to a table.
I have a table1 with a Column “Account”. I want to add through VBA 3 new columns as follows:
Sub Test2()
Dim Table As ListObject
Dim LC As ListColumn
Dim col As Integer
Dim i As Integer
‘ I add 3 Columns
For i = 1 To 3
‘Refer the tables
Set Table = ActiveSheet.ListObjects(“Table1”)
‘Add a column to Table1
Set LC = Table.ListColumns.Add
Select Case i ‘Set the header and write the formulas
Case 1
LC.Name = “NCOL1”
LC.DataBodyRange.Formula = “=(RIGHT(TABLE1[@ACCOUNT],3))”
Case 2
LC.Name = “NCOL2”
LC.DataBodyRange.Formula = “”
Case 3
LC.Name = “NCOL3”
LC.DataBodyRange.Formula = “=(IF(TABLE1[@NCOL2]=””””,TABLE1[@NCOL1],TABLE1[@NCOL2]))”
End Select
Next i
End Sub
‘With this test you can see that NCOL1 gives a result which refresh when you change column Account but NCOL 3 Does not refresh. It just do when you push F2 into the cell and it recalculates.
If you add a new row formula from NCOL1 appears but not into NCOL3. It seems the problem is that NCOL formula refers to an original column into the table but NCOL3 refer to the new columns added. Where can be the problem?
Your code tested fine for me, not sure what the problem is. Is it possible the column already exists? Excel will change the name of the right-most duplicated column name. So if you have a column name of “NCOL1” already, and you insert a column and name it “NCOL1”, Excel will change the right-most of the two to “NCOL12″.
I would recommend some error handling. You can check if a column exists in a table with a simple function…
[sourcecode language=””]Function TableColumnExists(ByVal ColumnName As String, ByVal Table As ListObject) As Boolean
On Error Resume Next
TableColumnExists = CBool(Table.ListColumns(ColumnName).Index <> 0)
On Error GoTo 0
End Function[/sourcecode]
Table are most useful. However, after the zillion lines of code posted here, and reading “Excel Tables, A Complete Guide” I find 2 very fundamental issues with Tables not covered. It could be I am most likely a second grader in class with Ph.D.’s.
#1There should be a way to format a Table in its entirety for font, size, etc. No luck finding that VBA procedure yet, or should I say that I can understand. A lot of gurus, while “apparently” smart programmers, cannot EFFECTIVELY communicate worth squat in English. Rather than simply “spit out code” take time to explain what that code actually does in plain good ole English. Simply, my opinion.
#2 Clearing a Table of it’s contents, yet retaining all formulas and formatting.
This code below works, i.e., formulas remain, but I fear it is deleting the font formatting Idon’t know for sure but read on):
Sub ClearTable()
‘Table name is DataEntry
‘Table is found on Sheet named – 313_Violation_DataEntry
Application.ScreenUpdating = False
With Sheet1.ListObjects(“DataEntry”)
If Not .DataBodyRange Is Nothing Then
.DataBodyRange.Delete
End If
End With
Application.ScreenUpdating = True
End Sub
The reason for thinking formatting is “axed” is I have formatted two columns in the Table a zillion times for Arial, 11 point. All the other columns are Arial 11 point. Yet, after I clear the Table, with the above code, and begin to re-enter new data, those columns revert back to Calibri 11 point. Yes, reset default to Arial 11 from Calibri in Options General. I have spent the whole of Memorial Day, today, trying to understand. Might as well be in front of the “box” – pouring down rain all weekend in Dallas. Suggestion, websites, books, comments, links, and education into these two (2) issues most welcomed and graciously appreciated.
Mort Wakeland
Dallas, TX
(ex Vietnam infantry sergeant- 101st Airborne Div – Remember those who gave the ultimate!)
Well I see I cannot even write – Tables (plural) are most useful.
Mort