how to fix starting point when counting rows?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

polarcap - 29 Aug 2008 16:08 GMT

is there a way to feed in the the starting cell to begin from when
counting the number of rows?

i can get the below to work in a macro

Dim countRows
countRows = Range("C9").End(xlDown).Row

but when i try to turn it into a function i cant figure out how to
give the starting cell as an argument

tks for any help

Reply to this Message

Pete_UK - 29 Aug 2008 23:13 GMT

Put "C9" (without the quotes) in cell A1, for example, then you can do
this:

=COUNT(INDIRECT(A1&":C100"))
or
=COUNTA(INDIRECT(A1&":C100"))

Is that what you mean?

Hope this helps.

Pete

> is there a way to feed in the the starting cell to begin from when
> counting the number of rows?
[quoted text clipped - 8 lines]
>
> tks for any help

Reply to this Message

Gary''s Student - 29 Aug 2008 23:32 GMT

Let's say there is nothing in column C except some value in C20. the macro:

Sub dural()
Dim countRows
countRows = Range("C9").End(xlDown).Row
MsgBox (countRows)
End Sub

will output 20. Now if we put:

=firstusedrowbelow(C9) somewhere else, it will also display 20. The UDF is:

Function FirstUsedRowBelow(r As Range) As Long
FirstUsedRowBelow = r.End(xlDown).Row
End Function

There is a danger with the function form. It is blind to changes in column
C because they are not arguments of the function.

So if you insert a value in C10, the macro would "see" it, the function
would not.

(unless you forced re-calculation.)

Signature

Gary''s Student - gsnu2007k

> is there a way to feed in the the starting cell to begin from when
> counting the number of rows?
[quoted text clipped - 8 lines]
>
> tks for any help

Reply to this Message

polarcap - 30 Aug 2008 15:25 GMT

On Aug 29, 6:32 pm, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:

> Let's say there is nothing in column C except some value in C20. the macro:
>
[quoted text clipped - 36 lines]
>
> - Show quoted text -

tks again for all your help. i was able to get what i was looking for
by ussing the cell address

Function countrows(cellstart) As Double

Dim countEm
countBars = Range(cellstart.Address).End(xlDown).Row

End Function

Reply to this Message

Pete_UK - 30 Aug 2008 20:37 GMT

Why do you:

Dim countem

and then not use it?

Also, your function is called countrows, but you use countBars in the
body of the function - does it work for you?

Pete

> tks again for all your help. i was able to get what i was looking for
> by ussing the cell address
[quoted text clipped - 5 lines]
>
> End Function

Reply to this Message