LAddress of last cell in worksheet

[Jim]

I need to know the address of the last cell (non-empty) on my worksheet. I
see that .Rows.Count and .Columns.Count seem to show only the max
size. "Last" is defined as a non-empty cell with the largest row and column
address.

thanks

 

[Rowan Drummond]

MsgBox Cells.SpecialCells(xlCellTypeLastCell).Address

will give you the last cell in the UsedRange although this may be empty
if you have deleted the contents of some cells.

Assuming you have headings in row 1 then:

Dim eRow As Long
Dim eCol As Integer
eCol = Cells(1, Columns.Count).End(xlToLeft).Column
eRow = Cells(Rows.Count, eCol).End(xlUp).Row
MsgBox Cells(eRow, eCol).Address

will give you the address of the last non-empty cell in the last column
with a heading.

Hope this helps
Rowan

 

[Norman Jones]

Hi Jim.

To find the last populated row and the last populated column, try the
following functions:

'=================>>
Public Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

'----------------->

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
'<<=================

Whether the cell with the largest column value or the cell with the largest
row value should be deemed as the last cell is for you to decide.

 

[Jim]

Thank you for ur fast and detailed reply