Sunday, July 20, 2008

Add Total Row to Excel Table using VBA

Add Total Row to Existing List Object using Excel VBA

Sub Add_TotalRow_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)

oLst.ShowTotals = True

' Change/Set the formatting of the Totals Row

oLst.TotalsRowRange.Font.Bold = True

oLst.TotalsRowRange.Font.Color = vbRed

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

ShowTotals method is used for appending a total row to the Excel List. TotalsRowRange is used for formatting Excel data


2 comments:

  1. Anonymous6:21 AM

    getting error in

    oWS = ActiveSheet

    ReplyDelete
  2. The code is wrong. Objects need to use Set.

    Set oWS = ActiveSheet

    Likewise with the ListObjects

    ReplyDelete

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