sum of last Current Region.?

[miqbal]

Hi Forum,

example:
[TABLE="width: 250"]
<tbody>[TR]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[TD]e
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sum (e5:e8)
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

suppose above is the data on sheet 1

I want to find out sum of last current region of column "E" starting from "5" to "8"

since I am new to VBA I tried below code but no success.

Sub findtotal()​

​

firstrow = Sheet1.Cells(Rows.Count, 5).End(xlUp).End(xlUp)​

​

lastrow = Sheet1.Cells(Rows.Count, 5).End(xlUp)​

​

MsgBox firstrow
MsgBox lastrow​

​

totalval = Application.WorksheetFunction.Sum(Sheet1.Range(firstrow, lastrow))​

​

MsgBox totalval​

​

End Sub​

"totalval" part is not working and I know the reason because syntax is not right.

please if someone help to resolve it and correct the code.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG09Mar58
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("E:E").SpecialCells(xlCellTypeConstants)
MsgBox Application.Sum(Rng.Areas(Rng.Areas.Count))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Joe4]

The issues you had with your code is that you were returning the values from cells E5 and E8, not the row numbers (you didn't notice that because your row numbers and values matched).
Also, in building your range, you included the row numbers but not the column references.

To carry on with your method, here is how you could get it to work:

Sub findtotal()

Dim firstrow As Long, lastrow As Long
Dim myRange As Range

firstrow = Sheet1.Cells(Rows.Count, 5).End(xlUp).End(xlUp)[COLOR=#ff0000].Row[/COLOR]
lastrow = Sheet1.Cells(Rows.Count, 5).End(xlUp)[COLOR=#ff0000].Row[/COLOR]
Set myRange = Sheet1.[COLOR=#ff0000]Range(Cells(firstrow, 5), Cells(lastrow, 5))[/COLOR]
totalval = Application.WorksheetFunction.Sum(myRange)
MsgBox totalval

End Sub

 

[Rick Rothstein]

Alternately (to Joe4's posting), you could have approached it this way where you work with the found cell rather than their row numbers (note that I changed some of your variable names to make them more descriptive and that I also declared all my variables as to their data type)...

[table="width: 500"]
[tr]
	[td]Sub FindTotal()

  Dim FirstCell As Range, LastCell As Range, TotalVal As Double
  
  Set FirstCell = Sheet1.Cells(Rows.Count, 5).End(xlUp).End(xlUp)
  Set LastCell = Sheet1.Cells(Rows.Count, 5).End(xlUp)
  
  MsgBox FirstCell.Row
  MsgBox LastCell.Row
  
  TotalVal = Application.WorksheetFunction.SUM(Range(FirstCell, LastCell))
  
  MsgBox TotalVal

End Sub[/td]
[/tr]
[/table]