Sunday, July 20, 2008

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

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

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

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


If Err <> 0 Then

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

Resume Next

End If

End Sub

No comments:

Post a Comment

Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.