List Column Ranges

[mgaria_sln]

I am attempting to apply xlTotalsCalculationSum to a range of cells in a table, but I cannot seem to get the range entered properly. Here is my current code.

ActiveSheet.ListObjects("TenantIncome").ListColumns("Period2").TotalsCalculation = xlTotalsCalculationSum
ActiveSheet.ListObjects("TenantIncome").ListColumns("Period3").TotalsCalculation = xlTotalsCalculationSum
ActiveSheet.ListObjects("TenantIncome").ListColumns("Period4").TotalsCalculation = xlTotalsCalculationSum
ActiveSheet.ListObjects("TenantIncome").ListColumns("Period5").TotalsCalculation = xlTotalsCalculationSum
ActiveSheet.ListObjects("TenantIncome").ListColumns("Period6").TotalsCalculation = xlTotalsCalculationSum
ActiveSheet.ListObjects("TenantIncome").ListColumns("Period7").TotalsCalculation = xlTotalsCalculationSum
ActiveSheet.ListObjects("TenantIncome").ListColumns("Period8").TotalsCalculation = xlTotalsCalculationSum
ActiveSheet.ListObjects("TenantIncome").ListColumns("Period9").TotalsCalculation = xlTotalsCalculationSum
ActiveSheet.ListObjects("TenantIncome").ListColumns("Period10").TotalsCalculation = xlTotalsCalculationSum
ActiveSheet.ListObjects("TenantIncome").ListColumns("Period11").TotalsCalculation = xlTotalsCalculationSum
ActiveSheet.ListObjects("TenantIncome").ListColumns("Period12").TotalsCalculation = xlTotalsCalculationSum
ActiveSheet.ListObjects("TenantIncome").ListColumns("Total").TotalsCalculation = xlTotalsCalculationSum

How can I simplify this into one line of code?

 

[pgc01]

Hi

Not quite 1 line of code, but

if the columns are contiguous you add the total to the first column and then fill it to the right, like:

ActiveSheet.ListObjects("TenantIncome").ListColumns("Period2").TotalsCalculation = xlTotalsCalculationSum

Range("TenantIncome[[#Totals],[period2]]").AutoFill Destination:=Range("TenantIncome[[#Totals],[Period2]:[Period12]]"), Type:=xlFillDefault

 

[mgaria_sln]

Hello PGC,

Thank you for your reply. I had tried that code originally, but unfortunately when the Fill executes it sets all columns in the provided range as =SUBTOTAL(109,[Period2]). It's filling like [Period2] is a locked reference.

Regards,

Martin

 

[pgc01]

Thank you for your reply. I had tried that code originally, but unfortunately when the Fill executes it sets all columns in the provided range as =SUBTOTAL(109,[Period2]). It's filling like [Period2] is a locked reference.

Hi Martin

That's strange. I had tried it with excel 2010 and it worked OK. Now I tried it with 2007 and it's working OK too.

Did you use the code exactly that I posted?

 

[mgaria_sln]

Good morning PGC,

Yes, I applied your code both modified and then unmodified. In my original post I accidently omitted Period1. Nevertheless, neither Fills correctly. I recently started using Excel2013, perhaps this is a bug in that version?

Here is the code in several forms: modified, unmodified, and the individual code for each row (which is the only one working at this time). At this point, I'm not overly stressed about having 10 additional lines. I'll just move forward and revisit it at a later date.

Thank you for your assistance!

Regards,

Martin

' *****************************
' Apply sums to a TenantIncome table
' *****************************

' ActiveSheet.ListObjects("TenantIncome").ListColumns("Period2").TotalsCalculation = xlTotalsCalculationSum
' Range("TenantIncome[[#Totals],[Period2]]").AutoFill Destination:=Range("TenantIncome[[#Totals],[Period2]:[Period12]]"), Type:=xlFillDefault

ActiveSheet.ListObjects("TenantIncome").ListColumns("Period1").TotalsCalculation = xlTotalsCalculationSum
Range("TenantIncome[[#Totals],[Period1]]").AutoFill Destination:=Range("TenantIncome[[#Totals],[Period1]:[Period12]]"), Type:=xlFillDefault

' Range("TenantIncome[[#Totals],[Period1]]").FormulaR1C1 = "=SUBTOTAL(109,[Period1])"
' Range("TenantIncome[[#Totals],[Period2]]").FormulaR1C1 = "=SUBTOTAL(109,[Period2])"
' Range("TenantIncome[[#Totals],[Period3]]").FormulaR1C1 = "=SUBTOTAL(109,[Period3])"
' Range("TenantIncome[[#Totals],[Period4]]").FormulaR1C1 = "=SUBTOTAL(109,[Period4])"
' Range("TenantIncome[[#Totals],[Period5]]").FormulaR1C1 = "=SUBTOTAL(109,[Period5])"
' Range("TenantIncome[[#Totals],[Period6]]").FormulaR1C1 = "=SUBTOTAL(109,[Period6])"
' Range("TenantIncome[[#Totals],[Period7]]").FormulaR1C1 = "=SUBTOTAL(109,[Period7])"
' Range("TenantIncome[[#Totals],[Period8]]").FormulaR1C1 = "=SUBTOTAL(109,[Period8])"
' Range("TenantIncome[[#Totals],[Period9]]").FormulaR1C1 = "=SUBTOTAL(109,[Period9])"
' Range("TenantIncome[[#Totals],[Period10]]").FormulaR1C1 = "=SUBTOTAL(109,[Period10])"
' Range("TenantIncome[[#Totals],[Period11]]").FormulaR1C1 = "=SUBTOTAL(109,[Period11])"
' Range("TenantIncome[[#Totals],[Period12]]").FormulaR1C1 = "=SUBTOTAL(109,[Period12])"
' Range("TenantIncome[[#Totals],[Total]]").FormulaR1C1 = "=SUBTOTAL(109,[Total])"

 

[pgc01]

Thanks for the feedback. I'll ask around to see if it runs OK in excel 2013.

 

[pgc01]

... and you are right.

RoryA was kind enough to try it in other versions of excel and it does not work in excel 2013.

Interesting enough, it works in excel 2016 !!!

So, it works in excel 2007, 2010 and 2016

Does not work in excel 2013

which means, IMO, a bad solution, not to use.