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 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


No comments:

Post a Comment

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group