Identify last row containing data

[MJKelly]

Hi,

How can I identify the last row containing data on a sheet where the
last entry could appear in any column from H to T? I can only think
of looping through the columns and comparing the lastrow number of
each and then identifying the highest value? Surely there is an
easier way?

Thanks,
Matt

 

[Ron de Bruin]

Hi Matt

See
http://www.rondebruin.nl/last.htm

 

[Chip Pearson]

Try code like

Dim RR As Range
Dim R As Range
Set RR = Worksheets(1).Range("H:T")
Set R = RR.Find("*", RR.Cells(RR.Cells.Count), _
xlValues, xlWhole, xlByRows, xlPrevious, False)
Debug.Print R.Address

This searches for any content at all in columns H to T, working
backwards sot he first cell found is the last used cell.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Rick Rothstein]

Two points on your Find statement. First, (and this may just be
misinterpreting what you mean by "any content") because you specified
xlValues, cells with formulas that return the empty string will be ignored
by your Find statement, even if that formula is in a lower row than a
non-empty cell). Second, since the search is progressing backwards (due to
the xlPrevious argument), specifying the first cell in the range will
accomplish the same thing as specifying a cell in the last row of the
worksheet within the range. So, instead of RR.Cells(RR.Cells.Count), I would
use RR(1) instead.