0

I have a vba that shows a totals row and sets specific columns to average. I want to set the number format of the totals row to be "0.00", but sometimes there are more or less rows, so I can't just select a specific range. I tried using objListObj.TotalsRowRange.NumberFormat but that doesn't seem to work. Any help would be greatly appreciated!

Sub averages()
'
' averages Macro
'
' Keyboard Shortcut: Ctrl+m
'
Dim objListObj As ListObject

ActiveSheet.ListObjects("Table1").Range.Select
    With ActiveSheet.ListObjects(1)
        .ShowTotals = True
        .ListColumns(6).TotalsCalculation = xlTotalsCalculationAverage
        .ListColumns(7).TotalsCalculation = xlTotalsCalculationAverage
        .ListColumns(8).TotalsCalculation = xlTotalsCalculationAverage
        .ListColumns(9).TotalsCalculation = xlTotalsCalculationAverage
        .ListColumns(10).TotalsCalculation = xlTotalsCalculationAverage
        .ListColumns(11).TotalsCalculation = xlTotalsCalculationAverage
        .ListColumns(12).TotalsCalculation = xlTotalsCalculationAverage
        .ListColumns(13).TotalsCalculation = xlTotalsCalculationAverage
        .ListColumns(14).TotalsCalculation = xlTotalsCalculationAverage
        .ListColumns(15).TotalsCalculation = xlTotalsCalculationAverage
        .ListColumns(16).TotalsCalculation = xlTotalsCalculationAverage
        .ListColumns(19).TotalsCalculation = xlTotalsCalculationAverage
        .ListColumns(21).TotalsCalculation = xlTotalsCalculationAverage
        .ListColumns(22).TotalsCalculation = xlTotalsCalculationAverage
        .ListColumns(23).TotalsCalculation = xlTotalsCalculationAverage
        .ListColumns(24).TotalsCalculation = xlTotalsCalculationAverage
        .ListColumns(25).TotalsCalculation = xlTotalsCalculationAverage
        .ListColumns(26).TotalsCalculation = xlTotalsCalculationAverage
        

    End With
    
End Sub
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Chelsea Ziss
  • 27
  • 4
  • 4
    `.TotalsRowRange.NumberFormat = "0.00"` works for me. What specifically happened or didn't happen on your end? – BigBen May 04 '23 at 15:27
  • I was overthinking it - I was adding objListObj. before TotalsRowRange -_- that did work, thank you! – Chelsea Ziss May 04 '23 at 16:31

2 Answers2

1

Another way is to add the total to each column and then remove it from the single column. Using this method you can change the number of columns in the table and move column 20 to a different location and it will still work.

Sub T()

    Dim lo As ListObject
    Set lo = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")
    
    With lo
        .ShowTotals = True
        
        'Add a total to each column in table.
        Dim col As ListColumn
        For Each col In .ListColumns
            col.TotalsCalculation = xlTotalsCalculationAverage
        Next col
        
        'Using the column name:
        .ListColumns("My Named Column").TotalsCalculation = xlTotalsCalculationNone 'Remove total from column.
        'Or can use the column number:
        '.ListColumns(5).TotalsCalculation = xlTotalsCalculationNone
    
        .TotalsRowRange.NumberFormat = "0.00"
    End With
    
End Sub
Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
0

I add an array with the column indexes to reduce the code using a loop.

Sub averages()
'
' averages Macro
'
' Keyboard Shortcut: Ctrl+m
'
   Dim objListObj As ListObject, totRng As Range, clmns As Variant, c As Long, lb As Long, ub As Long
   clmns  = Array(6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 19, 21, 22, 23, 24, 25, 26)
   lb = LBound(clmns)
   ub = UBound(clmns)
   Set totRng = Range("Table1[[#Totals]]")
   With ActiveSheet.ListObjects("Table1")
      .ShowTotals = True
      'Range("Table1[[#Totals]]").NumberFormat = "0.00"
      .TotalsRowRange.NumberFormat = "0.00"   ' @BigBen comment
      For c = lb To ub
         .ListColumns(clmns(c)).TotalsCalculation = xlTotalsCalculationAverage
      Next
   End With
End Sub
ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ
  • 1,658
  • 1
  • 4
  • 14