sum of a range starting from cell in fixed column to cell in last column

[SamP1983]

Hello,

I am building a dashboard which contains a revenue overview (monthly). The user of the dashboard selects the start month and end month (e.g. January - July). It would then look like this

[table="width: 500, align: center"]
[tr]
[td]SERVICES[/td]
[td]January[/td]
[td]February[/td]
[td]March[/td]
[td]April[/td]
[td]May[/td]
[td]June[/td]
[td]July[/td]
[td]Total[/td]
[/tr]
[tr]
[td]Product1[/td]
[td]152345[/td]
[td]435156[/td]
[td]549141[/td]
[td]234581[/td]
[td]645127[/td]
[td]512487[/td]
[td]157481[/td]
[td]sum[/td]
[/tr]
[tr]
[td]Product2[/td]
[td]32457[/td]
[td]38754[/td]
[td]64517[/td]
[td]54781[/td]
[td]51204[/td]
[td]54178[/td]
[td]48761[/td]
[td]sum[/td]
[/tr]
[/table]


I am trying to find a VBA code to calculate the sum per product. This range per product always starts with a fixed cell and has to end with the cell of the last column within the same row. However the last column can change (could be any month of the year).

I already found a way to capture the last column number using this:

Dim LastCol As Long
    
    With ActiveSheet
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    
    Cells(2, LastCol).Value = WorksheetFunction.Sum(Cells(2,2) ?????)

But I got stuck creating a WorksheetFunction.Sum that will calculate the sum per product for all months in each row (with a variable end month).

Does any one would have any advise?

Thank you in advance!

 

[njimack]

This could easily be achieved via formulae, but if your forced to use a VBA approach, you're nearly there.

Your last line of code should be:

Cells(2,LastCol).Value = Worksheetfunction.Sum(Cells(2,2),Cells(2,LastCol-1))

 

[SamP1983]

Hi Neil,

Thank you for your reply! I noticed that when I use the last line as you suggested it will only calculate the sum of the first and the last month (January + July in this case). However I am looking for a code that will calculate all the months (January + February + March + April + May + June + July in this example).

Would you have any other suggestions?

Thanks in advance!

 

[tsonko]

Hi Sam,

You need to use a Range in your case.

Cells(2,LastCol).Value = Worksheetfunction.Sum(Range(Cells(2,2),Cells(2,LastCol-1)))

 

[SamP1983]

Thanks a lot, that works perfectly!