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

Hi Matt

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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm



"MJKelly" wrote in message ...
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

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




On Wed, 24 Mar 2010 14:10:38 -0700 (PDT), MJKelly
wrote:

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

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.

--
Rick (MVP - Excel)



"Chip Pearson" wrote in message
...

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




On Wed, 24 Mar 2010 14:10:38 -0700 (PDT), MJKelly
wrote:

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