Count number of rows with actually selecting
 

I'm using "Selection.Count" to count the number of cells in a range. How
would I do this without actually visibly selecting the range i.e. I don't
want the active cell to change from that selected by the user when they
started my procedure.

thanks

...pc

Count number of rows with actually selecting
 

That would depend. Do you want the UsedRange

ACtivesheet.UsedRange.Cells.Count

from the activecell to the end of the currentregion

activecell.currentregion.Cells.Count

or something else?

--
HTH

Bob Phillips

"PC" <paulm dot c @ iol dot ie wrote in message
...
I'm using "Selection.Count" to count the number of cells in a range. How
would I do this without actually visibly selecting the range i.e. I don't
want the active cell to change from that selected by the user when they
started my procedure.

thanks

..pc

Count number of rows with actually selecting
 

One way:

numCells = rng.Count


where rng is the range of interest.

You almost never need to select anything - working with range objects
directly makes your code smaller, faster, and IMO easier to maintain.


In article ,
"PC" <paulm dot c @ iol dot ie wrote:

I'm using "Selection.Count" to count the number of cells in a range. How
would I do this without actually visibly selecting the range i.e. I don't
want the active cell to change from that selected by the user when they
started my procedure.

Count number of rows with actually selecting
 

Count is the property of a range. so something like this will work

Dim rng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
Set rng = wks.Range("B2:B100")

MsgBox rng.Count

HTH

Jim Thomlinson


"PC" wrote:

I'm using "Selection.Count" to count the number of cells in a range. How
would I do this without actually visibly selecting the range i.e. I don't
want the active cell to change from that selected by the user when they
started my procedure.

thanks

...pc

Count number of rows with actually selecting
 

And if the range is a collection of rows instead of cells, something
like the following will work to count the rows:

Dim rng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
Set rng = wks.Range("B2:C100").Rows

MsgBox rng.Count

Alan Beban

Jim Thomlinson wrote:
Count is the property of a range. so something like this will work

Dim rng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
Set rng = wks.Range("B2:B100")

MsgBox rng.Count

HTH

Jim Thomlinson


"PC" wrote:


I'm using "Selection.Count" to count the number of cells in a range. How
would I do this without actually visibly selecting the range i.e. I don't
want the active cell to change from that selected by the user when they
started my procedure.

thanks

...pc

Count number of rows with actually selecting
 

Thanks for your input - I worked out a solution to the problem from the
suggestions given


"PC" <paulm dot c @ iol dot ie wrote in message
...
I'm using "Selection.Count" to count the number of cells in a range. How
would I do this without actually visibly selecting the range i.e. I don't
want the active cell to change from that selected by the user when they
started my procedure.

thanks

..pc