Find last row AND last column and do some sums

[Alex Piotto]

Hi people!
So... see the situation below. I would like to have the sum of each row in a dynamic range, until the last column, also part of a dynamic range.
I may have 2 rows and 2 columns, or 50 rows and 50 columns... number of rows and columns is variable.

And here is the code so far... it surely add only 3 rows... but dynamically at least!

Private Sub CommandButton4_Click()

Range("I6", Cells(6, Columns.Count).End(xlToLeft)).Select
Range("I6", Cells(6, Columns.Count).End(xlToLeft)).Name = "MyRange"
Dim tcell As Range
Set tcell = Range("G6")
tcell.Formula = "=SUM(Myrange)"

Range("I7", Cells(7, Columns.Count).End(xlToLeft)).Select
Range("I7", Cells(7, Columns.Count).End(xlToLeft)).Name = "MyRange2"
Dim tcell2 As Range
Set tcell2 = Range("G7")
tcell2.Formula = "=SUM(Myrange2)"

Range("I8", Cells(8, Columns.Count).End(xlToLeft)).Select
Range("I8", Cells(8, Columns.Count).End(xlToLeft)).Name = "MyRange3"
Dim tcell3 As Range
Set tcell3 = Range("G8")
tcell3.Formula = "=SUM(Myrange3)"

End Sub

I will use the solution to learn something about loops... it is not a job, actually.

 

[My Aswer Is This]

Try this:
Script assumes you want to start on row 6

Sub Sum_Range()
'Modified 10/14/2020 4:12:21 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim LastColumn As Long
Lastrow = Cells(Rows.Count, "F").End(xlUp).Row

    For i = 6 To Lastrow
        LastColumn = Cells(i, Columns.Count).End(xlToLeft).Column
        Cells(i, "G").Value = Application.Sum(Cells(i, "I").Resize(, LastColumn))
    Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

I needed to modify my previous code.
Try this:
Script assumes you want to start on row 6

Sub Sum_Range()
'Modified  10/14/2020  10:45:05 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim LastColumn As Long
Lastrow = Cells(Rows.Count, "F").End(xlUp).Row

    For i = 6 To Lastrow
        LastColumn = Cells(i, Columns.Count).End(xlToLeft).Column
        Cells(i, "G").Value = Application.Sum(Cells(i, "I").Resize(, LastColumn - 8))
    Next
Application.ScreenUpdating = True
End Sub

 

[Alex Piotto]

Hi! Very simple and very effective... thank you.
The first one works fine too. Why

.Resize(, LastColumn - 8)

minus 8?

 

[My Aswer Is This]

Hi! Very simple and very effective... thank you.
The first one works fine too. Why

.Resize(, LastColumn - 8)

minus 8?

Well because we start looking for data in column 9
It's a little difficult to explain but it works without the -9 but really it's more proper to use it the last way.

Resize means look to the right till it finds no more data. Which is what Lastcolumn finds
Glad this works for you.

 

[Alex Piotto]

OK! thanks for the explaining too. The code with -8 give me error... with -9 it works.

 

[My Aswer Is This]

OK! thanks for the explaining too. The code with -8 give me error... with -9 it works.

You earlier said:
The first one works fine too. Why
This sounded like both worked. But now you say -8 gives you a error
It works for me. Glad you have it working for you.

 

[My Aswer Is This]

I now see if you have no figures in the row you will get a error. So if you have something in Column F but nothing in cells to the right you will get a error. I could put in a error catcher if needed like this:
Try this:

Sub Sum_Range()
'Modified  10/15/2020  2:08:53 AM  EST
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
Dim Lastrow As Long
Dim LastColumn As Long
Lastrow = Cells(Rows.Count, "F").End(xlUp).Row

    For i = 6 To Lastrow
        LastColumn = Cells(i, Columns.Count).End(xlToLeft).Column
        Cells(i, "G").Value = Application.Sum(Cells(i, "I").Resize(, LastColumn - 8))
    Next
Application.ScreenUpdating = True
Exit Sub
M:

MsgBox "We had a problem" & vbNewLine & "Row " & Rows(i).Row & "  Has no data to Sum"
Application.ScreenUpdating = True
End Sub