Convert Range to a ListObject using Excel VBA

Convert Range to Excel Table

Listobject provides more advantages than a simple Excel Range. A listobject becomes a datasource to play with. The following code converts the usedrange in the current worksheet to an Excel Table

Sub Convert_Range2Table()

Dim oWS As Worksheet ' Worksheet Object

Dim oRange As Range ' Range Object - Contains Represents the List of Items that need to be made unique

On Error GoTo Disp_Error

' ---------------------------------------------

' Coded by Shasur for www.vbadud.blogspot.com

' ---------------------------------------------

oWS = ActiveSheet

oRange = oWS.UsedRange

oWS.ListObjects.Add(xlSrcRange, oRange, , xlYes).Name = "FruitsList"

If Not oRange Is Nothing Then oRange = Nothing

If Not oWS Is Nothing Then oWS = Nothing

' --------------------

' Error Handling

' --------------------

Disp_Error:

If Err <> 0 Then

MsgBox(Err.Number & " - " & Err.Description, vbExclamation, "VBA Tips & Tricks Examples")

Resume Next

End If

End Sub