Neil Tenbrook


I'm trying to capture the sum of a highlighted range in an Excel Marco without using a loop. After selecting a range, I can capture the cell count with the following command:

X = Range(Selection, Selection.End(xlDown)).Count

Wondering if there is way to the sum as well. Excel can show the sum highlighted range, so it seems I should be able to replecate in VB.

Thanks,

Neil




Re: Excel-Capture the sum of Highlighted Range

Keithyboy1


See this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1493182&SiteID=1

It should be applicable to your scenario also.






Re: Excel-Capture the sum of Highlighted Range

Neil Tenbrook

Thanks. Your idea help me think of some different approaches and I arrived at these lines of code:

Range("l2").Select
Range(Selection, Selection.End(xlDown)).Select
y = Application.WorksheetFunction.Sum(Selection)

Any thoughts on what I have above I've used CountA in the past to capture number of rows, but the function seems to create big files.

I appreciate you taking the time to respond to my post.







Re: Excel-Capture the sum of Highlighted Range

Andy Pope

Hi,

You could try something like this. Usually you do not need to select ranges in order to reference them.

Code Snippet

MsgBox Application.WorksheetFunction.Sum(Range("I2:I" & Range("I2").End(xlDown).Row))
MsgBox Application.WorksheetFunction.Count(Range("I2:I" & Range("I2").End(xlDown).Row))
MsgBox Range("I2:I" & Range("I2").End(xlDown).Row).Rows.Count