Addition between two defined cells

suprsonic · #1 (**permalink**) February 18th, 2011, 12:52 PM

I work with long spreadsheets that vary in length but never more than 30,000 rows. Within these rows are some empty rows and a header, which all need to be ignored. Because of these conditions I am trying to define a range from top to bottom of each column. I have the code written to the point that I am ready to sum (or avg) between the two defined cells "toprow" & "bottomrow". I can't get the code to perform the calculation.
Below is what I have so far. The last line is where I'm stuck.

Sub macro()

col = Selection.Column
Application.Goto reference:="r10C"
Selection.End(xlUp).Select
toprow = ActiveCell.Value
Application.Goto reference:="r30000C"
Selection.End(xlUp).Select
bottomrow = ActiveCell.Value
ActiveCell.Offset(3, 0).Select

ActiveCell.Formula = "=Sum.range(toprow:bottomrow)"

End Sub

mtranchi · #2 (**permalink**) February 18th, 2011, 09:06 PM

you can use the worksheetfunction method and just put in the entire column as excel will ignore rows below the used range

Code:

'When you use the With statement, anything you start with a period will refer to that object. Note that the range object starts with a period.
With Sheets("MySheet")
    'Sums all entries in column A
    WorksheetFunction.Sum(.Range("A:A"))
End with

Need to download code?

Navigation