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.

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

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

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Alberto Pinto" wrote in message
...
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.

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" wrote in message
...
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.

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" wrote in message
...
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.