how to fix starting point when counting rows?

  • Thread starter Thread starter polarcap
  • Start date Start date
P

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
 
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
 
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.)
 
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 -
Click to expand...

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
 
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
 
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
 
Back
Top