Count Num and Sum functions as a variable
 

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
---------------------------------
Chris Freeman
IT Project Coordinator

Count Num and Sum functions as a variable
 

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

--
Rick (MVP - Excel)


"Chris Freeman" wrote in message
...
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
---------------------------------
Chris Freeman
IT Project Coordinator

Count Num and Sum functions as a variable
 

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

Count Num and Sum functions as a variable
 

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


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Chris Freeman" wrote in message
...
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
---------------------------------
Chris Freeman
IT Project Coordinator

Count Num and Sum functions as a variable
 

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)


"Chris Freeman" wrote in message
...
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


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Chris Freeman" wrote in message
...
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
---------------------------------
Chris Freeman
IT Project Coordinator

Count Num and Sum functions as a variable
 

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
--
Chris Freeman
IT Project Coordinator


"Rick Rothstein" wrote:

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)


"Chris Freeman" wrote in message
...
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


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Chris Freeman" wrote in message
...
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
---------------------------------
Chris Freeman
IT Project Coordinator