Convert Range To Table Excel VBA Macro

In this post we see how to convert a range with data into a table with Excel VBA macros. They are different VBA objects altogether – Range vs ListObject. Tables are a most efficient way to organize the raw data and refer to dynamic data that can expand across rows or columns. Tables can also be easily styled. The macro example below removes any previous table with a given name, converts a range with data into a table, and applies a specific style.

 

Macro code:

 

 Sub ConvertRangeToTable()
 
 Dim tblName As String, tblRange As Range, tblList As ListObject
 
 tblName = "MyTable"
 Set tblRange = Selection.CurrentRegion
 
 'delete if table with that name already exists
 If ActiveSheet.ListObjects.Count > 0 Then ActiveSheet.ListObjects(tblName).Unlist
 
 'add table
 Set tblList = ActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, _
 Source:=tblRange, xlListObjectHasHeaders:=xlYes)
 
 'format table
 With tblList
     .Name = tblName
     .TableStyle = "TableStyleDark2"
 End With
 
 End Sub

  

Macro explained:

• We declare variables to store the name of t,he table (tblName), target range (tblRange), and outcome table (tblList). Then we define the name and set the target range to the current region around the selection. Alternatively, we can use a “fixed” range address, or simply refer to the used range in a given worksheet.
• Then we check whether a table already exists in that sheet and delete it if it has the same name. Alternatively, we can choose to delete the table regardless of the name or loop through all the tables and delete only the one with a given name (e.g. tblName) or all of them.

 tblName = "MyTable"
 If ActiveSheet.ListObjects.Count > 0 Then
     For Each tbl In ActiveSheet.ListObjects
         If tbl.Name = tblName Then tbl.Unlist
     Next tbl
 End If

 

• Then we add a ListObject object to the target range that converts the range into a table. The parameters SourceType, Source, and Headers are used to determine the source type and  source range (specifically for that type), and whether the source range has headers or not. The source type can be one of the following: xlSrcExternal (0), xlSrcRange (1), xlSrcXml (2), xlSrcQuery (3), xlSrcModel  (4, which is a PowerPivot model).
• Next, we format the table (ListObject). It is important to name the table to later be able to find it and target it if needed. We can choose a table style, otherwise it applies the default style (usually TableStyleLight9). The list of table styles is available in the ribbon when selecting the table. We can also use other properties to choose whether to show row or column stripes or not (properties ShowTableStyleRowStripes, ShowTableStyleColumnStripes).
• Other properties of the ListObject object allow to choose whether to enable filtering and show the filter drop-down (ShowAutoFilter and ShowAutoFilterDropDown), or show totals if the table has numerical data and get/set any other properties of the totals range (ShowTotals and TotalsRowRange).

 

This is how we convert a range with data into a table in Excel VBA.

 

Other macros:

• How to make Function Return Array with Excel VBA macros
• How to launch a Color Picker Dialog with Excel VBA macros
• How to Export Data As PDF in Excel with VBA macros