Add Columns to Excel List Object using VBA / Add a new column to Excel Table using VBA
As discussed in previous blog posts, one of the ‘sleek’ advantage that a listobject gives us is to treat it as a datasource/ a data table. Here we can experience the same in adding a new column, which gets data from existing columns
Sub Add_ListColumn_2_ExistingTable()
Dim oWS As Worksheet ' Worksheet Object
Dim oRange As Range ' Range Object - Contains Represents the List of Items that need to be made unique
Dim oLst As ListObject ' List Object
Dim oLC As ListColumn ' List Column Object
On Error GoTo Disp_Error
' ---------------------------------------------
' Coded by Shasur for www.vbadud.blogspot.com
' ---------------------------------------------
oWS = ActiveSheet
If oWS.ListObjects.Count = 0 Then Exit Sub
oLst = oWS.ListObjects(1)
oLC = oLst.ListColumns.Add
oLC.Name = "Total Price"
oLC.DataBodyRange = "=[Price]*[Availability]"
If Not oLC Is Nothing Then oLC = Nothing
If Not oLst Is Nothing Then oLst = 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
Excel List before Column Addition
Excel List after Column Additon (Note the column values are generated automatically)Refer (http://vbadud.blogspot.com/2008/07/convert-range-to-listobject-using-excel.html) for creating ListObjects in Excel
Posts
i have tried the code what u have provided. It says compiler error .. like "Invalid use of property" in
ReplyDeleteoLst = oWS.ListObjects(1)
...
Rajesh
ReplyDeleteCan you try the following code
Sub Add_ListColumn_2_ExistingTable()
Dim oWS As Worksheet ' Worksheet Object
Dim oRange As Range ' Range Object - Contains Represents the List of Items that need to be made unique
Dim oLst As ListObject ' List Object
Dim oLC As ListColumn ' List Column Object
On Error GoTo Disp_Error
' ---------------------------------------------
' Coded by Shasur for www.vbadud.blogspot.com
' ---------------------------------------------
oWS = ActiveSheet
If oWS.ListObjects.Count = 0 Then Exit Sub
Set oLst = oWS.ListObjects(1)
Set oLC = oLst.ListColumns.Add
oLC.Name = "Total Price"
oLC.DataBodyRange = "=[Price]*[Availability]"
If Not oLC Is Nothing Then Set oLC = Nothing
If Not oLst Is Nothing Then Set oLst = Nothing
If Not oWS Is Nothing Then Set 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
HI...THIS IS VAISHALI...
ReplyDeleteCAN IT BE DONE WHERE DATA TO BE FILLED COMES FROM DATABASE??
can anyone help me how to add additional column between two columns in excel with out changing VBA code and to modify itself automatically in VBA code excel
ReplyDelete