Identify last row containing data




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?


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.

Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC

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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question