GetUsedRange Property does not release RAM.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have encountered the problem with a Worksheet that does not release RA
after a row delete that is discussed in Article Q163263. The solutio
suggested in Q163263 does not work for Excel 9.0.2720

Given 21 rows of data on a worksheet, if I delete the 21st row, the
run the macro
Sub Reset_Range(
ActiveSheet.UsedRang
End Su

Then I use the Edit Menu - Go To, Special, Last Cel
Excel 9 still selects the rightmost cell in row 21, should be row 20
If I save the workbook, then the UsedRange property is reset, as i
was with Office 95.
Q163263 says the UsedRange property will release the unused RAM as of Excel 97. I
doesn't. What happened between Office 97 and Office 2000

I'm conducting these same operations using Autmation with MFC and getting the same results.
 
UsedRange is a property, not a method, so your code does
nothing. I guess the KB article is just wrong.
AFAIK saving the file & re-opening it is the only way to
reset the used range.


Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
I have encountered the problem with a Worksheet that does not release RAM
after a row delete that is discussed in Article Q163263. The solution
suggested in Q163263 does not work for Excel 9.0.2720.

Given 21 rows of data on a worksheet, if I delete the 21st row, then
run the macro:
Sub Reset_Range()
ActiveSheet.UsedRange
End Sub

Then I use the Edit Menu - Go To, Special, Last Cell
Excel 9 still selects the rightmost cell in row 21, should be row 20.
If I save the workbook, then the UsedRange property is reset, as it
was with Office 95.
Q163263 says the UsedRange property will release the unused RAM as of Excel 97. It
doesn't. What happened between Office 97 and Office 2000?

I'm conducting these same operations using Autmation
Click to expand...
with MFC and getting the same results.
 
I was pretty upset moving to 2000 finding that 'dummy =
UsedRange.Rows.Count' no longer worked.
In XL97, just accessing that property would reset the UsedRange. From 2000
onward it appears it's only ever reset on save.

This routine will find the last occupied cell within the UsedRange. Be sure
to Activate the sheet you're after first.

Sub test()
Dim lngLastRow As Long, lngLastCol As Long

On Error Resume Next
lngLastRow = 1: lngLastCol = 1
With ActiveSheet.UsedRange
lngLastRow = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
lngLastCol = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByColumns, xlPrevious).Column
.Cells(lngLastRow, lngLastCol).Select
End With
End Sub


Chelms said:
I have encountered the problem with a Worksheet that does not release RAM
after a row delete that is discussed in Article Q163263. The solution
suggested in Q163263 does not work for Excel 9.0.2720.

Given 21 rows of data on a worksheet, if I delete the 21st row, then
run the macro:
Sub Reset_Range()
ActiveSheet.UsedRange
End Sub

Then I use the Edit Menu - Go To, Special, Last Cell
Excel 9 still selects the rightmost cell in row 21, should be row 20.
If I save the workbook, then the UsedRange property is reset, as it
was with Office 95.
Q163263 says the UsedRange property will release the unused RAM as of Excel 97. It
doesn't. What happened between Office 97 and Office 2000?

I'm conducting these same operations using Autmation with MFC and getting
Click to expand...
the same results.
 
Sub piuhpji()
ActiveSheet.UsedRange
End Sub

The above works fine for me in simple cases when I delete row 21 as
described below, in Excel 2000.
But I agree that there are a number of more complex cases when it wont work.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
This is worrisome. Sometimes it works, sometimes it doesn't.

It consistently works if I run the macro on a fresh worksheet. However, when I do a GetUsedRange Invoke from MFC, it always fails. In fact if I run the Macro on the worksheet following MFC, the Macro fails. More to follow.
 
Back
Top