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

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

--
Rick (MVP - Excel)


"Chris Freeman" <(E-Mail Removed)> wrote in message
news:1776B21A-0E5E-4EC8-BD99-(E-Mail Removed)...
>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

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

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" <(E-Mail Removed)> wrote in message
> news:1776B21A-0E5E-4EC8-BD99-(E-Mail Removed)...
> >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
>
>

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" <(E-Mail Removed)> wrote in message
news:BDD55956-4E54-4BBF-BEDA-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:1776B21A-0E5E-4EC8-BD99-(E-Mail Removed)...
>> >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
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:BDD55956-4E54-4BBF-BEDA-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:1776B21A-0E5E-4EC8-BD99-(E-Mail Removed)...
> >> >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
> >>
> >>
>
>