+ Reply to Thread
Results 1 to 6 of 6

Last filled row

  1. #1
    Alberto Pinto
    Guest

    Last filled row

    Hi!

    How can I know what is the last row filled with sometinhg on a page.
    I want to get always the last row of a list but that list is variable.

    Thanks in advance.

  2. #2
    Paul Lautman
    Guest

    Re: Last filled row

    Alberto Pinto wrote:
    > Hi!
    >
    > How can I know what is the last row filled with sometinhg on a page.
    > I want to get always the last row of a list but that list is variable.
    >
    > Thanks in advance.


    Are you talking in VBA? If so here is a function and a sub:

    Sub LastCell()
    'Ctrl-L


    On Error GoTo blanksheet


    Cells(Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows).Row, _
    Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByColumns).Column).Select
    Exit Sub
    blanksheet:
    Range("A1").Select
    End Sub

    Function lc(ws As Worksheet) As Range
    Dim LastRow&, LastCol%

    ' Error-handling is here in case there is not any
    ' data in the worksheet

    On Error GoTo blanksheet

    With ws

    ' Find the last real row

    LastRow& = .Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows).Row

    ' Find the last real column

    LastCol% = .Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByColumns).Column

    End With

    ' Finally, initialize a Range object variable for
    ' the last populated row.

    Set lc = ws.Cells(LastRow&, LastCol%)
    Exit Function
    blanksheet:
    Set lc = ws.Cells(1, 1)

    End Function



  3. #3
    Alberto Pinto
    Guest

    Re: Last filled row

    No, indeed not. I was talking in Excel functions

    Paul Lautman wrote:
    > Alberto Pinto wrote:
    >> Hi!
    >>
    >> How can I know what is the last row filled with sometinhg on a page.
    >> I want to get always the last row of a list but that list is variable.
    >>
    >> Thanks in advance.

    >
    > Are you talking in VBA? If so here is a function and a sub:
    >
    > Sub LastCell()
    > 'Ctrl-L
    >
    >
    > On Error GoTo blanksheet
    >
    >
    > Cells(Cells.Find(What:="*", _
    > SearchDirection:=xlPrevious, _
    > SearchOrder:=xlByRows).Row, _
    > Cells.Find(What:="*", _
    > SearchDirection:=xlPrevious, _
    > SearchOrder:=xlByColumns).Column).Select
    > Exit Sub
    > blanksheet:
    > Range("A1").Select
    > End Sub
    >
    > Function lc(ws As Worksheet) As Range
    > Dim LastRow&, LastCol%
    >
    > ' Error-handling is here in case there is not any
    > ' data in the worksheet
    >
    > On Error GoTo blanksheet
    >
    > With ws
    >
    > ' Find the last real row
    >
    > LastRow& = .Cells.Find(What:="*", _
    > SearchDirection:=xlPrevious, _
    > SearchOrder:=xlByRows).Row
    >
    > ' Find the last real column
    >
    > LastCol% = .Cells.Find(What:="*", _
    > SearchDirection:=xlPrevious, _
    > SearchOrder:=xlByColumns).Column
    >
    > End With
    >
    > ' Finally, initialize a Range object variable for
    > ' the last populated row.
    >
    > Set lc = ws.Cells(LastRow&, LastCol%)
    > Exit Function
    > blanksheet:
    > Set lc = ws.Cells(1, 1)
    >
    > End Function
    >
    >


  4. #4
    Bob Phillips
    Guest

    Re: Last filled row


    =LOOKUP(2,1/(A1:A1000<>""),A1:A1000)

    --

    HTH

    Bob Phillips

    (remove nothere from the email address if mailing direct)

    "Alberto Pinto" <[email protected]> wrote in message
    news:[email protected]...
    > Hi!
    >
    > How can I know what is the last row filled with sometinhg on a page.
    > I want to get always the last row of a list but that list is variable.
    >
    > Thanks in advance.




  5. #5
    Alberto Pinto
    Guest

    Re: Last filled row

    thanks but this doesn't work. Don't you know any other way?

    Alberto

    Bob Phillips wrote:
    > =LOOKUP(2,1/(A1:A1000<>""),A1:A1000)
    >
    > --
    >
    > HTH
    >
    > Bob Phillips
    >
    > (remove nothere from the email address if mailing direct)
    >
    > "Alberto Pinto" <[email protected]> wrote in message
    > news:[email protected]...
    >> Hi!
    >>
    >> How can I know what is the last row filled with sometinhg on a page.
    >> I want to get always the last row of a list but that list is variable.
    >>
    >> Thanks in advance.

    >
    >


  6. #6
    Paul Lautman
    Guest

    Re: Last filled row

    If this list is contiguous in a column then =COUNTA(A:A) will tell you the
    row number and
    =OFFSET(A1,COUNTA(A:A)-1,0,1,1) will give you the value.

    Alberto Pinto wrote:
    > thanks but this doesn't work. Don't you know any other way?
    >
    > Alberto
    >
    > Bob Phillips wrote:
    >> =LOOKUP(2,1/(A1:A1000<>""),A1:A1000)
    >>
    >> --
    >>
    >> HTH
    >>
    >> Bob Phillips
    >>
    >> (remove nothere from the email address if mailing direct)
    >>
    >> "Alberto Pinto" <[email protected]> wrote in message
    >> news:[email protected]...
    >>> Hi!
    >>>
    >>> How can I know what is the last row filled with sometinhg on a page.
    >>> I want to get always the last row of a list but that list is
    >>> variable. Thanks in advance.





+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1