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" <matt.j.kelly@gmail.com> wrote in message news:0b0627b5-6d67-4c1b-8205-7d96fd77b732@v20g2000yqv.googlegroups.com...
> 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
<matt.j.kelly@gmail.com> 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" <chip@cpearson.com> wrote in message 
news:451lq51afptiv55dec4oecth6map41nl07@4ax.com...
>
> 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
> <matt.j.kelly@gmail.com> 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