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

SERVICES	January	February	March	April	May	June	July	Total
Product1	152345	435156	549141	234581	645127	512487	157481	sum
Product2	32457	38754	64517	54781	51204	54178	48761	sum

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!