How can I quickly find the last used row and column of a worksheet?

I appreciate your help, -John

Use code like

Dim LastCell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.UsedRange.SpecialCells(xlCellTypeLastCell)
Debug.Print LastCell.Address


Note, though, that this LastCell may not contain any data. It is in
the last row that has any data in it and in the last column that has
any data in it. E.g.,

X X
X X
X LC

Where X is data and LC is the LastCell, which may be empty.

If you need the last cell that has data, you need to decide whether
you want the right-most column or the highest-number row. For example,


X X X
X X 11
X
22


In this example, is 11 or 22 the last cell? It depends on what you are
looking for and what you need to accomplish with the last cell.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Mon, 23 Feb 2009 09:19:02 -0800, John
wrote:

How can I quickly find the last used row and column of a worksheet?

I appreciate your help, -John

Be careful with UsedRange. The range inside gets expanded just by
talking to the cells even if there's nothing in it. I always use .Find
as a result.

Maury

This being the case, how does one "delete the last row, column entry" if a
cell was used erroneously? I keep getting a compatibilty error in 2007 from
a previous version, where the cell used was "outside" 255 X 65535.

"Maury Markowitz" wrote:

Be careful with UsedRange. The range inside gets expanded just by
talking to the cells even if there's nothing in it. I always use .Find
as a result.

Maury

The way to avoid trying to do something with a blank cell, that you think
has data, is to test it with an If...Then statement. If it is not empty,
equal to zero or null then do something with it, else do nothiing.
Recognizing that xl2007 has considerably more rows and columns than previous
versions, all code from previous versions should be reviewed for possible
errors related to those differences.


"Terry" wrote in message
...
This being the case, how does one "delete the last row, column entry" if a
cell was used erroneously? I keep getting a compatibilty error in 2007
from
a previous version, where the cell used was "outside" 255 X 65535.

"Maury Markowitz" wrote:

Be careful with UsedRange. The range inside gets expanded just by
talking to the cells even if there's nothing in it. I always use .Find
as a result.

Maury

Well I ran some code to delete any/all(hopefully) outside the normal area,
but still get the compatibility warning. After checking the last row/column
again now I find blank cells.

"JLGWhiz" wrote:

The way to avoid trying to do something with a blank cell, that you think
has data, is to test it with an If...Then statement. If it is not empty,
equal to zero or null then do something with it, else do nothiing.
Recognizing that xl2007 has considerably more rows and columns than previous
versions, all code from previous versions should be reviewed for possible
errors related to those differences.


"Terry" wrote in message
...
This being the case, how does one "delete the last row, column entry" if a
cell was used erroneously? I keep getting a compatibilty error in 2007
from
a previous version, where the cell used was "outside" 255 X 65535.

"Maury Markowitz" wrote:

Be careful with UsedRange. The range inside gets expanded just by
talking to the cells even if there's nothing in it. I always use .Find
as a result.

Maury



.

Give these a try...

LastUsedRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlRows).Row

LastUsedColumn = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

--
Rick (MVP - Excel)


"John" wrote in message
...
How can I quickly find the last used row and column of a worksheet?

I appreciate your help, -John