Macro - Go To Last

[jereece]

Is there a way I can use a Macro in Excel to go to the last cell that has data? I have a spreadsheet for my checkbook. It is getting rather long. I would prefer when I open it for the focus to move to the last entry.

I appreciate any suggestions.

Jim

 

[john471]

Private Sub Workbook_Open()
   ActiveSheet.Rows(ActiveSheet.Range("A65536:A65536").End(xlUp).Row).Select
End Sub

 

[zibo]

this will search the value of cells until it hits a blank

sub find_end()
Do until activecell.value=""=true
activecell.select
if activecell.value=""=false then
selection.offset(1,0).select
else
end if
loop


this will work for one column.. you can put all of this in a loop function to move columns and find the last empty cell as well

 

[shades]

And in anticipation of XL 12 with 1M rows, you could use this to determine the last row, which works no matter how many rows can be in the worksheet:

    Dim FinalRow As Long
    FinalRow = Range("A" & Rows.Count).End(xlUp).Row

________
Alaska Medical Marijuana

 

[jereece]

None of these seems to work. I am trying to create a checkbook register (see attached). Knowing that the register will get quite long, I want a macro (Autpen) to move the focus to the last cell that has something entered into it. Any help is appreciated.

 

[john471]

Jim,

You need to:-

1) put the code under the Open event of the Workbook
To do this, go into the visual basic editor, double click on the "ThisWorkbook" icon.

2) Change the column being assessed to "B", as your column "A" is blank (code per below)

3) copy and paste the following code

Private Sub Workbook_Open()
    Dim FinalRow As Long
    FinalRow = Range("B" & Rows.Count).End(xlUp).Row
    ActiveSheet.Rows(FinalRow).Select
End Sub

Your display should then look similar to the picture (in attached zip file)...

This will work - when you open the workbook it will select the last row - this applies to the worksheet that was active when you saved it, so if you had the instruction sheet active when you last saved, that sheet will have its last row selected.

HTH

Regards

John

 

[HaHoBe]

Hi, shades,

and what about the prior-Excel97 worksheets which only feature 16384 rows? Please find attached a sample of an Excel5.0 worksheet...

Ciao,
Holger

 

[Dudley]

I'm having a similar problem - finding the row with the last occupied cell - and I'm having trouble getting my little head around the end(xlup) thing. What if the last row with a cell of data in it is blank in column "a"? Is there a way to code the Control-End keystroke?

Thanks!!!!