Excel VBA Create Table from Cell Range: VBA Code Snippet Example

This Excel VBA create Table from cell range code snippet example helps you:

• Create an Excel Table;
• From a cell range.

Excel VBA Create Table from Cell Range Code Snippet Template

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-create-table-range/

Dim ExcelTableObjectVariable As ListObject

Set ExcelTableObjectVariable = WorksheetObjectReference.ListObjects.Add(SourceType:=xlSrcRange, _
    Source:=RangeObjectReference, _
    XlListObjectHasHeaders:=xlYesxlNoOrxlGuess)

For these purposes:

• ExcelTableObjectVariable is the name of the object variable representing the newly created Excel Table.
• WorksheetObjectReference is a reference to the Worksheet object representing the worksheet where the newly created Excel Table is stored.
• RangeObjectReference is a reference to the Range object representing the cell range containing the source data for the newly created Excel Table.
• xlYesxlNoOrxlGuess is one of the following 3 built-in constants (or values) from the XlYesNoGuess enumeration:
  • xlGuess (0).
  • xlYes (1).
  • xlNo (2).

Excel VBA Create Table from Cell Range Example Macro

The Excel VBA create Table from cell range example macro below:

• Creates an Excel Table whose source data is stored in cells A6 to J16 (Source:=.Range(“A6:J16”)) of the worksheet named “Create Table from Cell Range” in the workbook where the example macro is stored (With ThisWorkbook.Worksheets(“Create Table from Cell Range”)). The Excel Table has headers (XlListObjectHasHeaders:=xlYes).
• Assigns the reference to the newly created Excel Table to the MyExcelTable object variable.

Sub CreateTableFromCellRange()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/vba-create-table-range/
    
    'Declare object variable to represent Excel Table
    Dim MyExcelTable As ListObject
    
    '(1) Create Excel Table from cells A6 to J16 in the "Create Table from Cell Range" worksheet inside this workbook; and
    '(2) Assign the newly created Excel Table to the MyExcelTable object variable
    With ThisWorkbook.Worksheets("Create Table from Cell Range")
        Set MyExcelTable = .ListObjects.Add(SourceType:=xlSrcRange, _
                                            Source:=.Range("A6:J16"), _
                                            XlListObjectHasHeaders:=xlYes)
    End With
    
End Sub

The GIF at the top of this post shows the results I obtain when executing this Excel VBA create Table from cell range example macro.

Excel VBA Create Table from Cell Range Explanation

The ListObject object represents an Excel Table.

Use the ListObjects.Add method to create a new ListObject object (representing an Excel Table).

The ListObjects.Add method accepts 6 parameters:

• SourceType;
• Source;
• LinkSource;
• XlListObjectHasHeaders;
• Destination; and
• TableStyleName.

The Excel VBA create Table from cell range code snippet template I explain above works with the following 3 parameters:

(1) SourceType:

A built-in constant (or value) from the XlListObjectSourceType enumeration, indicating the Excel Table's data source type.

Set the SourceType parameter to xlSrcRange (1) to create an Excel Table from a cell range.

(2) Source:

When creating an Excel Table from a cell range, a Range object representing the applicable data source.

(3) XlListObjectHasHeaders:

A built-in constant (or value) from the XlYesNoGuess enumeration, indicating whether the Excel Table's data source has headers.

• • xlGuess (0).
  • xlYes (1).
  • xlNo (2).

If the data source has no headers, Excel automatically generates headers when creating the Excel Table.

As a general rule: Omit the LinkSource and Destination parameters when creating an Excel Table from a cell range.

More Excel Macro and VBA Training Materials and Resources

You can find more Excel Macro and VBA Tutorials (including other VBA code snippet examples) in the organized Tutorials Archive: Click here to visit the Archives.

If you want to save time when working with macros and VBA, you may be interested in AutoMacro. AutoMacro:

• Is an add-in for VBA.
• Installs directly into the Visual Basic Editor (VBE).

Depending on the version, AutoMacro comes loaded with:

• Code generators;
• An extensive code library;
• The ability to create your own code library; and
• Many other helpful time-saving tools and utilities.

Click here to learn more about AutoMacro (affiliate link).