cell function to get table length... Help--

[Guest]

This it a part from an old macro I wrote a while back.
Now trying to get a cell function from it.

' Function to provide # of last line of table
as it is edited (row # needed for % is table)
'----------------------------------------
' ------ Clen (column length -finds last row) ------
Public Function CLen() As Integer
Range("A500").Select ' below max table length
Selection.End(xlUp).Select
CLen = ActiveCell.Row
' **?? number displayed is row last edited !!!
End Function
' -----------------------------

Now the function runs when row is deleted
does not run when row is inserted

How can I set this up so:
1) it runs when table changes size (can be by several ways
usually insert text copy, row insert, delete row)
2) Get correct line number (where function is placed at
end of col A)

 

[Barrie Davidson]

On 2002-03-08 07:41, Anonymous wrote:
This it a part from an old macro I wrote a while back.
Now trying to get a cell function from it.

' Function to provide # of last line of table
as it is edited (row # needed for % is table)
'----------------------------------------
' ------ Clen (column length -finds last row) ------
Public Function CLen() As Integer
Range("A500").Select ' below max table length
Selection.End(xlUp).Select
CLen = ActiveCell.Row
' **?? number displayed is row last edited !!!
End Function
' -----------------------------

Now the function runs when row is deleted
does not run when row is inserted

How can I set this up so:
1) it runs when table changes size (can be by several ways
usually insert text copy, row insert, delete row)
2) Get correct line number (where function is placed at
end of col A)

You can change your function, to address question 1, to:

Public Function CLen() As Integer
Application.Volatile
CLen = Range("A65536").End(xlUp).Row
' **?? number displayed is row last edited !!!
End Function

I don't think you can build something in the function to address question 2 (but I'm bound to be wrong, someone else on the board will provide an answer and I'll learn something new. That's the beauty of this board ).

Regards,

 

[Guest]

THANKS, its a start... Its been awhile since I have done much VB programming in Excel, but its as fun as I remember. haha