|  | how to fix starting point when counting rows? | | |
| | | polarcap |  |
|  Posted: Fri Aug 29, 2008 3:08 pm Post subject: how to fix starting point when counting rows? |  |
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 |
| |
| | | Gary''s Student | |
| | Posted: Fri Aug 29, 2008 8:32 pm Post subject: RE: how to fix starting point when counting rows? | |
| |  | |
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
"polarcap" wrote:
| Quote: | 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 | |
| | Posted: Fri Aug 29, 2008 10:13 pm Post subject: Re: how to fix starting point when counting rows? | |
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
On Aug 29, 4:08 pm, polarcap <mem_...@yahoo.com> wrote:
| Quote: | 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 |
|
| |
| | | polarcap | |
| | Posted: Sat Aug 30, 2008 2:25 pm Post subject: Re: how to fix starting point when counting rows? | |
| | | |
On Aug 29, 6:32 pm, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:
| Quote: | 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
"polarcap" wrote:
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- Hide quoted text -
- 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 | |
| | Posted: Sat Aug 30, 2008 7:37 pm Post subject: Re: how to fix starting point when counting rows? | |
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
On Aug 30, 3:25 pm, polarcap <mem_...@yahoo.com> wrote:
| Quote: |
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 |
|
| |
|
|
