Find last row AND last column and do some sums

Alex Piotto

Board Regular
Joined
Jul 5, 2016
Messages
82
Office Version
  1. 2007
Platform
  1. Windows
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.

excelsample.jpg


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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Jul 5, 2016
Messages
82
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
Glad this works for you.
 

Alex Piotto

Board Regular
Joined
Jul 5, 2016
Messages
82
Office Version
  1. 2007
Platform
  1. Windows
OK! thanks for the explaining too. The code with -8 give me error... with -9 it works.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,322
Messages
5,641,529
Members
417,214
Latest member
qweb

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top