# Find last row AND last column and do some sums

#### Alex Piotto

##### Board Regular
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!

VBA Code:
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.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### My Aswer Is This

##### Well-known Member
Try this:
Script assumes you want to start on row 6
VBA Code:
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

##### Well-known Member
I needed to modify my previous code.
Try this:
Script assumes you want to start on row 6
VBA Code:
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

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

VBA Code:
.Resize(, LastColumn - 8)

minus 8?

#### My Aswer Is This

##### Well-known Member

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

VBA Code:
.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

#### Alex Piotto

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

#### My Aswer Is This

##### Well-known Member
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

##### Well-known Member
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:
VBA Code:
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

Replies
6
Views
65
Replies
3
Views
76
Replies
4
Views
201
Replies
5
Views
62
Replies
1
Views
126

1,130,258
Messages
5,641,162
Members
417,195
Latest member
Vishal kumar

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back