Vba Number Format for only Totals Calculation for ListObject

Question

I want to create a custom currency number format only in the total row table if there is a solution and which I mark the yellow color in the screenshot.

Thanks jack

Sub test2()
Dim objListObj As ListObject
Set objListObj = Sheets("test").ListObjects(1)
objListObj.ShowTotals = True
With Sheets("test").ListObjects("Table1")
.ListColumns("Total").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("Pot. :").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("Total End :").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("PRICE2").TotalsCalculation = xlTotalsCalculationSum
End With
End Sub

e.g. `.ListColumns("Total").TotalsRowRange.NumberFormat = "#,##0.00 $"`? — VBasic2008, Feb 25 '22 at 09:13

score 2 · Accepted Answer · answered Feb 25 '22 at 10:30

2

Excel Table (`ListObject`): `TotalsCalculation` & `TotalsRowRange`

Option Explicit

Sub test2()
    
    Dim Headers As Variant
    Headers = Array("Total", "Pot. :", "Total End :", "PRICE2")
    
    With ThisWorkbook.Worksheets("test").ListObjects("Table1")
        .ShowTotals = True
        Dim n As Long
        For n = LBound(Headers) To UBound(Headers)
            With .ListColumns(Headers(n))
                .TotalsCalculation = xlTotalsCalculationSum
                .Parent.TotalsRowRange.Columns(.Index) _
                    .NumberFormat = "#,##0.00 $"
            End With
        Next n
    End With

End Sub

answered Feb 25 '22 at 10:30

VBasic2008

44,888
5
17
28

Thank you for your reply. It works perfectly and one more how to add the word in column A in the total row with the word "Grand Total" – roy Feb 25 '22 at 12:55
`.TotalsRowRange.Cells(1).Value = "Grand Total"` right after `.ShowTotals = True`. – VBasic2008 Feb 25 '22 at 12:59
VBasic2008 , I've had a previous post maybe you can help me. [link](https://stackoverflow.com/questions/71169347/want-to-convert-formula-to-vba-array) – roy Feb 26 '22 at 01:44

score 0 · Answer 2 · answered Feb 25 '22 at 09:16

I recorded a macro and ended up with something like this:

Selection.NumberFormat = "#,###,##0.0000000000000 [$?-x-xbt1]"

How did I find this format? Well, I clicked right in a cell, asked for "Format cells", I chose "Number", "Currency", I chose the one which resembled the most to what I wanted, and clicked on "Custom" in order to see the actual formatting rule, which I can modify afterwards, as in this example:

(The formatting rule, you can see in the "Type" field, gets filled in automatically.)

Vba Number Format for only Totals Calculation for ListObject

2 Answers2

Excel Table (ListObject): TotalsCalculation & TotalsRowRange

Excel Table (`ListObject`): `TotalsCalculation` & `TotalsRowRange`