Count Num and Sum functions as a variable

[Chris Freeman]

I have a sheet that I need to count the number of items in a column and also
sum them. I can add a formula to cell, collect the values to a variable, then
clear the cell, but I was trying to figure out a more elegant internal code.
I wanted to caputure the number of items in the column without looping.
problem is that some rows are blank, and Selection.Rows.Count brings back all
rows.

Thanks

 

[Rick Rothstein]

Look at the WorksheetFunction Property (of the Application object)... the
count and sum functions are all available through it.

 

[marcus]

Hi Chris

This should place the number of used cells after the last used cell in
Column A. It looks at the rows in Column A2 to the last used cell and
counts only those rows which are not empty. You can adapt to suit.

Take care

Marcus

Sub CountUsed()
Dim Test As Integer
Dim lw As Integer
lw = Range("A" & Rows.Count).End(xlUp).Row
Test = Application.WorksheetFunction.CountA(Range("A2:A" & lw))
Range("A" & lw + 1).Value = Test
End Sub

 

[Chris Freeman]

Rick,
I have no idea what you just said. I know a little programming but, what is
WorksheetFunction Property?

I items listed when I press F2 in the code window, but how do I add this to
my code?
--
Chris Freeman
IT Project Coordinator

Look at the WorksheetFunction Property (of the Application object)... the
count and sum functions are all available through it.

 

[Rick Rothstein]

It's hard to say how you should add this to your code as you didn't post any
code (which is why I gave you the general-type answer I did. Here is a
made-up example that may help you see how to integrate it into your code.

RangeTotalVariable = WorksheetFunction.Sum(Range("A1:A100"))

CountNumbersVariable = WorksheetFunction.Count(Range("A1:A100"))

CountTextCellsVariable = WorksheetFunction.CountA(Range("A1:A100"))

Of course, these constructions can be more complicated than I have shown
depending on what you are trying to do.

--
Rick (MVP - Excel)

Rick,
I have no idea what you just said. I know a little programming but, what
is
WorksheetFunction Property?

I items listed when I press F2 in the code window, but how do I add this
to
my code?

 

[Chris Freeman]

Rick,
Perfect, I just couldn't get the syntax right. I kept trying to use
Selection.rows.count and that just brought back all rows in the selected
area. Thanks, and I'll try to be more precise and include code next time.

Thanks