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


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Chris Watts" <ng@ctwatts.plus.com> wrote in message 
news:euXh6vGoKHA.3948@TK2MSFTNGP06.phx.gbl...
>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
>
> 

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


"Don Guillett" <dguillett1@gmail.com> wrote in message 
news:O8ENu4GoKHA.1544@TK2MSFTNGP06.phx.gbl...
>      lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
>      , , , xlByRows, xlPrevious).Row
>
>
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett@gmail.com
> "Chris Watts" <ng@ctwatts.plus.com> wrote in message 
> news:euXh6vGoKHA.3948@TK2MSFTNGP06.phx.gbl...
>>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
>>
>>
> 

Chris

See how Debra codes it with a macro for all sheets in workbook.

http://www.contextures.on.ca/xlfaqApp.html#Unused


Gord Dibben  MS Excel MVP

On Thu, 28 Jan 2010 23:22:37 -0000, "Chris Watts" <ng@ctwatts.plus.com>
wrote:

>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
>
>
>"Don Guillett" <dguillett1@gmail.com> wrote in message 
>news:O8ENu4GoKHA.1544@TK2MSFTNGP06.phx.gbl...
>>      lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
>>      , , , xlByRows, xlPrevious).Row
>>
>>
>> -- 
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett@gmail.com
>> "Chris Watts" <ng@ctwatts.plus.com> wrote in message 
>> news:euXh6vGoKHA.3948@TK2MSFTNGP06.phx.gbl...
>>>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
>>>
>>>
>> 
>

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


"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:5094m517mb0j6flusk2r7mkt1995k899v3@4ax.com...
> Chris
>
> See how Debra codes it with a macro for all sheets in workbook.
>
> http://www.contextures.on.ca/xlfaqApp.html#Unused
>
>
> Gord Dibben  MS Excel MVP
>
> On Thu, 28 Jan 2010 23:22:37 -0000, "Chris Watts" <ng@ctwatts.plus.com>
> wrote:
>
>>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
>>
>>
>>"Don Guillett" <dguillett1@gmail.com> wrote in message
>>news:O8ENu4GoKHA.1544@TK2MSFTNGP06.phx.gbl...
>>>      lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
>>>      , , , xlByRows, xlPrevious).Row
>>>
>>>
>>> -- 
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguillett@gmail.com
>>> "Chris Watts" <ng@ctwatts.plus.com> wrote in message
>>> news:euXh6vGoKHA.3948@TK2MSFTNGP06.phx.gbl...
>>>>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
>>>>
>>>>
>>>
>>
>