Reset last row used

  • Thread starter Thread starter Chris Watts
  • Start date Start date
C

Chris Watts

I have a worksheet where I have deleted the data form the last batch of rows
and wish to reset (using VBA) the last row used returned by Excel 2007 to
the genuinely occuped rows.

I have tried using: ActiveSheet.UsedRange but it doesn't do the trick.
That is probably because the rows from which I have deleted the data have
still retained their formatting. I do want to retain the formatting (eg row
height etc) as I wish to add further data in due course.

Any suggestions as to how I can reset what Excel returns as last row used in
these circumstances?

TIA
Chris
 
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
 
Thanks Don, I'll give that a try.
At first reading I would expect that simply to return the row number and not
reset the used range - I hope that I am wrong!

cheers
Chris
 
Thanks, Don and Gord,

Debra's code (and manual technique) does reset the range but destroys the
formatting on the deleted cells so that subsequent data entry there has the
default rather than the desired format.

Don's suggestion just returns the last row number but doesn't reset the
range.

cheers
Chris
 
Back
Top