Last Used Cell

globalStar · Mar 28th 2007

I have a worksheet and it has so many rows
I want when I open the workbook goes to last used cell in a column.

I tried this but it works only manual:

Code

Sub LastCellBeforeBlankInRow()
   Range("A1").End(xlToDown).Select
End Sub

I put it here but not work

Code

Private Sub Workbook_Open()


Sheets("Database").Select
LastCellBeforeBlankInRow


End Sub

Any suggestions are wellcome

**StephenR** · Mar 28th 2007

Re: Last Used Cell

It's xlDown not xlToDown. Btw this will only find the last cell if there are no blanks, otherwise best to start at the bottom and work up.

Robert B · Mar 28th 2007

Re: Last Used Cell

Hi

this will identify the last used cell

Code

LastCell = ActiveCell.SpecialCells(xlCellTypeLastCell)

The Row & Column numbers associated with the last cell can be found by

Code

LastRow = LastCell.Row
LastCol = LastCell.Column

Each assumes that the variables have been properly declared.

HTH

Robert

**Reafidy** · Mar 28th 2007

Re: Last Used Cell

Hi,

See Here - Ozgrid Find Last Cell

I beleive this is one of the best.

Code

Sub Demo()
Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select
End Sub

Brandtrock · Mar 28th 2007

Re: Last Used Cell

Another bit of reading for your consideration.

Regards,

globalStar · Mar 29th 2007

Re: Last Used Cell

I have coded this and it's work?
[VBA]
Sub LastCellAfterUsedInColumn()
Dim Lastrow As Range
Set Lastrow = Range("A3").End(xlDown)
Application.Goto Reference:=Lastrow, Scroll:=True
End Sub
[/vba]

**royUK** · Mar 29th 2007

Re: Last Used Cell

Why not

Code

Sub LastCellAfterUsedInColumn()
Cells(Rows.Count,1).End(xlUp).Select
End Sub

Brandtrock · Mar 29th 2007

Re: Last Used Cell

Quote from globalStar

I have coded this and it's work?
[VBA]
Sub LastCellAfterUsedInColumn()
Dim Lastrow As Range
Set Lastrow = Range("A3").End(xlDown)
Application.Goto Reference:=Lastrow, Scroll:=True
End Sub
[/vba]

Display More

You will see the problem with this method if you do this:

Enter some text or numbers in A1:A5
Skip A6
Enter some text or numbers in A7:A10

Run your code.

Where are you?

Place the cursor back in A1.
Now run Roy's code on the same sheet.

Now where are you?

Roy's method will work correctly as he is going to the last row, first column using the Cells(Rows.Count,1) code. This will go to the bottom of 2003 and 2007 worksheets, even though they have different numbers of rows.

Quote from RoyUK

Why not
[VBA]

Sub LastCellAfterUsedInColumn() Cells(Rows.Count,1).End(xlUp).Select End Sub
[/vba]

The FIND method listed elsewhere in this thread is also a better solution than the code you posted.

Hope this helps,

Last Used Cell

Participate now!

Share