Identify last row containing data

  • Thread starter Thread starter MJKelly
  • Start date Start date
M

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
 
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
 
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.
 
Back
Top