Reset last row used

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
 
D

Don Guillett

lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
 
C

Chris Watts

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
 
C

Chris Watts

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
 

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

Top