Last Cell

[averageguy]

How do I return the cell that is the intersection of the last column with
the last row of a worksheet that has data. When I press F5 and check last
cell I get a cell that is beyond this intersection.
I am trying to learn VBA by combining reading a textbook with looking at
code in a macro module.

 

[RB Smissaert]

This will do it:

Function LastCell(ws As Worksheet) As Range

Dim LastRow As Long
Dim LastCol As Long

'Error-handling is here in case there is no data in the sheet
On Error GoTo NODATA

With ws
'Find the last real row
LastRow = .Cells.Find(what:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
'Find the last real column
LastCol = .Cells.Find(what:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
'Initialize a Range object variable for
'the last populated row and column.
Set LastCell = .Cells(LastRow, LastCol)
End With

Exit Function
NODATA:

On Error GoTo 0
Set LastCell = ws.Cells(1)

End Function

Use it like this:

Sub Test()
LastCell(ActiveSheet).Select
End Sub


RBS

 

[Norman Jones]

Hi AverageGuy,

When I press F5 and check last
cell I get a cell that is beyond this intersection.

RB has given you a method of establishing the last cell.

You have, however, already discovered that your worksheet extends "way
beyond" the last cell. That being the case, I would suggest that you visit
Debra Dalgleish's page on resetting the used range:

http://www.contextures.com/xlfaqApp.html#Unused

Debra shows manual and VBA methods to rectify matters.

 

[Guest]

Sub check()

Dim lastcell As Range
Dim ws As Worksheet

Set ws = ActiveSheet

Set lastcell = ws.UsedRange.SpecialCells(xlCellTypeLastCell)

lastcell.Select


End Sub