Open Workbook at Last Row With Data

[raymaster98]

Using Excel 2003.

Is there a way to open a workbook so that the last row with data in it is always in view? The workbook has data entered into it by many people. Occasionally, the workbook is saved in a way so that the next person that opens the workbook has to scroll down to the next available row for data entry. Unfortunately, some do not know Excel that well and become confused. It would be simpler if Excel would open to the row auromatically. Any help would be most appreciated.


Thanks, Kenny

 

[hippiehacker]

code has to go to thisworkbook code

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    With Sh
        .Cells(.UsedRange.Rows.Count, 1).Select
    End With
End Su

 

[raymaster98]

Thanks for the response. I am new to VBA. I have copied/pasted the code into the VBA editor box with the macro name of "Last". I am getting an error. Am I doing something wrong?

 

[Aladin Akyurek]

Using Excel 2003.

Is there a way to open a workbook so that the last row with data in it is always in view? The workbook has data entered into it by many people. Occasionally, the workbook is saved in a way so that the next person that opens the workbook has to scroll down to the next available row for data entry. Unfortunately, some do not know Excel that well and become confused. It would be simpler if Excel would open to the row auromatically. Any help would be most appreciated.


Thanks, Kenny

Let Sheet1 house the data of interest...

[TABLE="width: 144"]
<colgroup><col style="width: 48pt;" span="3" width="64"> <tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]GAD[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]108WZ[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]0.059[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Field-1[/TD]
[TD="class: xl64, bgcolor: transparent"]Field-2[/TD]
[TD="class: xl64, bgcolor: transparent"]Field-3[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]JAD[/TD]
[TD="class: xl63, bgcolor: transparent"]117WZ[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0.672[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]KAD[/TD]
[TD="class: xl63, bgcolor: transparent"]107WZ[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0.243[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]JAD[/TD]
[TD="class: xl63, bgcolor: transparent"]128WZ[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0.448[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]LAD[/TD]
[TD="class: xl63, bgcolor: transparent"]108WZ[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0.309[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CAD[/TD]
[TD="class: xl63, bgcolor: transparent"]192WZ[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0.932[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]ZAD[/TD]
[TD="class: xl63, bgcolor: transparent"]191WZ[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0.396[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]XAD[/TD]
[TD="class: xl63, bgcolor: transparent"]168WZ[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0.057[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]VAD[/TD]
[TD="class: xl63, bgcolor: transparent"]122WZ[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0.393[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]VAD[/TD]
[TD="class: xl63, bgcolor: transparent"]161WZ[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0.588[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]GAD[/TD]
[TD="class: xl63, bgcolor: transparent"]108WZ[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0.059[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

A1, copied across:

=LOOKUP(9.99999999999999E+307,$C$4:INDEX($C:$C,ROWS($C:$C)),A$4:INDEX(A:A,ROWS(A:A)))

This formula will always show the last record. It uses the numeric column. If everything is text, replace the big number with the following big string: REPT("z",255).

If you want the record somewhere else, the formula becomes:

=LOOKUP(9.99999999999999E+307,Sheet1!$C:$C,Sheet1!A:A)

Hope this set up meets your needs.

 

[raymaster98]

Thanks Aladin. You have helped me many times before. I probably wasn't clear enough, but I am looking for a way for the Excel workbook I have to always open to the row in which data was last entered. If someone typed data in cell D12000, and then scrolled back to the top of the workbook, the next person who wants to enter data has to scroll to the last row of data (row 12000) or know a shortcut to the last row. My goal is to simplify the process so that Excel will always open so that the last row of data is in view (no scrolling). Thanks, Kenny

 

[Aladin Akyurek]

Thanks Aladin. You have helped me many times before. I probably wasn't clear enough, but I am looking for a way for the Excel workbook I have to always open to the row in which data was last entered. If someone typed data in cell D12000, and then scrolled back to the top of the workbook, the next person who wants to enter data has to scroll to the last row of data (row 12000) or know a shortcut to the last row. My goal is to simplify the process so that Excel will always open so that the last row of data is in view (no scrolling). Thanks, Kenny

Kenny: You are right. That requires VBA programming.

 

[hippiehacker]

Thanks for the response. I am new to VBA. I have copied/pasted the code into the VBA editor box with the macro name of "Last". I am getting an error. Am I doing something wrong?

Open VBA Editor with ALT+F11
on the left handside you should see your excel file double click on ThisWorkbook

at the right side you have your code area paste the code in it and save that it
don't change the line Private Sub Workbook_SheetActivate(ByVal Sh as Object) as its a workbook event

Private Sub Workbook_SheetActivate(ByVal Sh As Object)    
 With Sh         
.Cells(.UsedRange.Rows.Count, 1).Select     
End With End Sub

 

[Peter_SSs]

It is unclear if the workbook has more than one sheet, but in any case an issue with hippiehacker's suggestion is that opening a workbook does not trigger the worksheet activate event. It is also not unusual for the 'UsedRange' to be quite different to the range that actually contains data.


My suggestion is this.

Private Sub Workbook_Open()
    Dim lr As Long, scrlrw As Long
    
    With ActiveSheet
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        scrlrw = IIf(lr > 10, lr - 10, 1)
        Application.Goto Reference:=.Range("A" & lr + 1)
        ActiveWindow.ScrollRow = scrlrw
    End With
End Sub

This code must also be placed in the 'ThisWorkbook' section in the vba window.

 

[raymaster98]

Thanks to everyone for their input. I thought this may go unsolved. This forum is great. I tried Peter's code and so far, it seems to work perfectly. Thanks Again, Kenny