how to fix starting point when counting rows?

[polarcap]

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

 

[Pete_UK]

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

 

[Gary''s Student]

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.)

 

[polarcap]

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.)
--
Gary''s Student - gsnu2007k









- 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

 

[Pete_UK]

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

 

[mikerickson]

This formula, =COUNTA(C:C)- COUNTA(C1:C8) should return the same value as countRows in the OP, if there are no blank rows to interupt the .End