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.
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" <[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.
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.
>
>
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks