Cell Address of last piece of data on spreadsheet

[Chris W.]

Can someone please refresh me on the code to locate the
last occupied cell on a spread sheet, even if data on
spreadsheet is non-contiguous. Specifically Row number &
Column number of the last cell used. I am not referring
to .Find("*")

Thank you.

 

[Oliver Ferns via OfficeKB.com]

Hi,
you could use

Set myrange = ThisWorkbook.Sheets(1).UsedRange

Then
myrange.rows.count
will give you row number and
myrange.columns.count
will give you column number

Or you could use
Set myrange = ThisWorkbook.Sheets(1).Cells.SpecialCells(xlLastCell)

However the UsedRange property is less than perfect. For example, if you
have a spreadsheet with 10 rows of data over 10 columns then UsedRange will
show J10 correctly as the last cell. If you now clear the contents of 5
rows at the bottom and 5 columns at the right UsedRange will show E5 as the
last cell. Right? Wrong!! The property will not reset until you save the wb
or delete the cells that were used. The same is true of .SpecialCells
(xlLastCell) incredibly. So the question you are asking; ie how to
accurately find the last cell to contain data is ambiguous as how do you
define the last cell? If there is data in IV1 and data in A65536 and no
other data on a sheet, which one do you consider to be the 'last cell'?

O

 

[Guest]

or just use the SpecialCells method:

Function LastUsedCell() As String

LastUsedCell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address

End Function

Patrick Molloy
Microsoft Excel MVP

 

[EvolBob]

A slow formula way for rows:

=SUMPRODUCT(MAX(ROW(1:2000)*(1:2000<>"")))

and this for columns:

=SUMPRODUCT(MAX(COLUMN(1:256)*(1:2000<>"")))

The larger the range, the slower the calculation.
In code:

MsgBox [SUMPRODUCT(MAX(COLUMN(1:256)*(1:2000<>"")))] _
& " columns"


Regards
Robert McCurdy