Attempt to Select Last Row problem

Tachyon

New Member
Joined
Apr 10, 2009
Messages
11
:confused: Would anyone have any ideas as to why this little piece of code results in the selection of Row 5003 (x= 5003)?
There is 'apparently' nothing in the spreadsheet except in the first 6 rows; or is it possible that there are 'things' in cells that I don't know about? And if so, any ideas as to how to find out what they are?
Thanks

Private Sub EndKeyPress()
Application.EnableEvents = False
x = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row
Range("A" & x).Select
Application.EnableEvents = True
End Sub
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
L

Legacy 68668

Guest
UsedRange/SpecialCells(xlLastCell)

refers to the range that has been changed incl the cell with

1) format changed
2) cleared

so if you accidentally select any cells in 5003 and hit the delete key or change the cell format, it shoud appy upto that cell, even you only have 6 rows of data.

Code:
Range("a" & Rows.Count).End(xlUp)).Select
 

Tachyon

New Member
Joined
Apr 10, 2009
Messages
11
Ahhhh . . . I see!

Do you know how I can do this if I have rows with no data and columns with no data but I want it to go to the line after the last line where any one of the columns in the last line may have data? I'm using columns a thru J.
 
L

Legacy 68668

Guest
one way
Code:
Sub test()
Dim LastColumn As Long, LastRow As Long
LastRow = Cells.Find("*", , , , xlByRows, xlPrevious).Column
LastColumn = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
MsgBox Cells(LastRow, LastColumn).Address
End Sub
 

Tachyon

New Member
Joined
Apr 10, 2009
Messages
11

ADVERTISEMENT

Thanks Seiya . . . a modified version of your code works perfectly :)
 
L

Legacy 68668

Guest
OOps
shold be
Rich (BB code):
Sub test()
Dim LastColumn As Long, LastRow As Long
LastRow = Cells.Find("*", , , , xlByRows, xlPrevious).Row
LastColumn = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
MsgBox Cells(LastRow, LastColumn).Address
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,551
Messages
5,596,797
Members
414,103
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top