Reading Last Row of Data

[sslack]

Is this possible?

I have an excel document with two worksheets in it. Worksheet #2 is
just a bunch of data sorted by date. Worksheet #1 displays data from
the last row of worksheet #2.

Is it possible to have excel detect the last row of data on Worksheet
#2 and display it on Worksheet #1?

Right now I am manually changing the cell references on worksheet #1 as
I add data in worksheet #2.

Any help would be greatly appreciated!

 

[Bernard Liengme]

=LOOKUP(9.99999999999999E+307,Sheet2!A:A)

Change Sheet2 to whatever the sheet is names
Change A:A to whatever column you need
best wishes

 

[Dave Peterson]

If you don't have any gaps (empty cells) in your data in worksheet #2, you could
use a formula like:

=index(sheet2!a:a,counta(sheet2!a:a))

to return the last cell in column A.

 

[Ron de Bruin]

Try this

Sub copytest()
Dim lr As Long
lr = LastRow(Sheets("Sheet2"))
Sheets("Sheet2").Rows(lr).Copy Sheets("Sheet1").Range("A1")
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

 

[Gord Dibben]

=LOOKUP(9.99999999999999E+307,Sheet1!A:A) will return the last numeric value
of Sheet1 column A


=LOOKUP(REPT("z",255),Sheet1!A:A) will return the last text entry
of Sheet1 column A

Enter either in A1 of Sheet2 and drag/copy across as far as you wish.


Gord Dibben Excel MVP

 

[Ron de Bruin]

Oops, you want a worksheet function

--
Regards Ron de Bruin
http://www.rondebruin.nl

Try this

Sub copytest()
Dim lr As Long
lr = LastRow(Sheets("Sheet2"))
Sheets("Sheet2").Rows(lr).Copy Sheets("Sheet1").Range("A1")
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

 

[mhoffmeier]

I had a similar hurdle and used =counta(a:a) in j1, every row had a
value in column a, so that counted the rows. I then referenced that
formula in =SUMPRODUCT(--(OFFSET($A$1,0,0,$J$1)='SF
Analysis'!$C$1),--(OFFSET($C$2,0,0,$J$1)>=$A9),--(OFFSET($C$2,0,0,j$J$1)<$A10),(OFFSET(!$F$2,0,0,$J$1)))*$C$7

Basicaly, the count of the rows is used to create the height ofthe
range with offset.

Oops, you want a worksheet function