cell function to get table length... Help--
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

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

  1. #1
    Guest

    Default

     
    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)

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    Guest

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com