When I delete information from a large number of cells (or clear contents) on
a worksheet, the amount of memory allocated and the file size of the
application apparently is not re-allocated to reflect that these cells are no
longer being used. How can I "release" the cells whose contents have been
deleted or cleared? Thanks.
--
Mike McCullough

Maybe resetting the last used cell would help:

Debra Dalgleish shows techniques to reset that last used cell:
http://contextures.com/xlfaqApp.html#Unused



MMcCullough wrote:

When I delete information from a large number of cells (or clear contents) on
a worksheet, the amount of memory allocated and the file size of the
application apparently is not re-allocated to reflect that these cells are no
longer being used. How can I "release" the cells whose contents have been
deleted or cleared? Thanks.
--
Mike McCullough

--

Dave Peterson

Since you have deleted the data already, you should be able to hit save.
This should reduce the sheet to the used cells.

"MMcCullough" wrote:

When I delete information from a large number of cells (or clear contents) on
a worksheet, the amount of memory allocated and the file size of the
application apparently is not re-allocated to reflect that these cells are no
longer being used. How can I "release" the cells whose contents have been
deleted or cleared? Thanks.
--
Mike McCullough

i have a similar problem

when removing a large picture form the spreadsheet the filesize stays big,
2MB rather than 15K

I have copied over the rows (1-8) that were not part of the image object
(kind of hard to find whre the object lives) into a new worksheet in the same
file, but the size stays the same.

ONly when creating new spreadsheet and then copying and pasting the 8 rows
into there and then saving this new file results in a file of 15K size.

Any idea how to release memory from a large image object that has been
deleted, but its memory is still lingering in a library of some sort witiin
that xls?

thx
Arjen
"Dave Peterson" wrote:

Maybe resetting the last used cell would help:

Debra Dalgleish shows techniques to reset that last used cell:
http://contextures.com/xlfaqApp.html#Unused



MMcCullough wrote:

When I delete information from a large number of cells (or clear contents) on
a worksheet, the amount of memory allocated and the file size of the
application apparently is not re-allocated to reflect that these cells are no
longer being used. How can I "release" the cells whose contents have been
deleted or cleared? Thanks.
--
Mike McCullough

--

Dave Peterson

Are you sure you got all the shapes?

Maybe you have a few (or one giant) shape that's not visible. A little macro
may help find those shapes.

Option Explicit
Sub testme()
Dim myShape As Shape
For Each myShape In ActiveSheet.Shapes
myShape.Visible = True
Next myShape
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Arjen wrote:

i have a similar problem

when removing a large picture form the spreadsheet the filesize stays big,
2MB rather than 15K

I have copied over the rows (1-8) that were not part of the image object
(kind of hard to find whre the object lives) into a new worksheet in the same
file, but the size stays the same.

ONly when creating new spreadsheet and then copying and pasting the 8 rows
into there and then saving this new file results in a file of 15K size.

Any idea how to release memory from a large image object that has been
deleted, but its memory is still lingering in a library of some sort witiin
that xls?

thx
Arjen
"Dave Peterson" wrote:

Maybe resetting the last used cell would help:

Debra Dalgleish shows techniques to reset that last used cell:
http://contextures.com/xlfaqApp.html#Unused



MMcCullough wrote:

When I delete information from a large number of cells (or clear contents) on
a worksheet, the amount of memory allocated and the file size of the
application apparently is not re-allocated to reflect that these cells are no
longer being used. How can I "release" the cells whose contents have been
deleted or cleared? Thanks.
--
Mike McCullough

--

Dave Peterson


--

Dave Peterson

example at http://www.solvesmart.com/xls/

THANKS for the quick reply
I created a button, assigned the macro, and ran it. Nothing showed up. I
also went to EDIT - GOTO SPECIAL and selected OBJECTS, also nothing.

I ahve in the meanwhile inserted a new worksheet SHEET1, and deleted the
oringial sheet all together. the good thing should be, there is no old sheet,
the bad thing might be that i have distroyed the only link to the lingereing
memory

Perhaps then only via VB i can delete it, by releasing all memory of all
objects, if i knew where it was hiding

I have about 100 files that all have a large image that i will have to
remove, and i also have to reduce the filesizes from 2 MB to 15 or so K for
each.

I have posted the test spreadsheet on http://www.solvesmart.com/xls/
as TEST01.xls

Let me know if there is still hope, also considering that hte other 99 files
actually have the picture object still visibily sitting there, and i wil
lhave to find a method to delete those. This testing file is just showing the
after math of deleting the object from the first file

THanks

"Dave Peterson" wrote:

Are you sure you got all the shapes?

Maybe you have a few (or one giant) shape that's not visible. A little macro
may help find those shapes.

Option Explicit
Sub testme()
Dim myShape As Shape
For Each myShape In ActiveSheet.Shapes
myShape.Visible = True
Next myShape
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Arjen wrote:

i have a similar problem

when removing a large picture form the spreadsheet the filesize stays big,
2MB rather than 15K

I have copied over the rows (1-8) that were not part of the image object
(kind of hard to find whre the object lives) into a new worksheet in the same
file, but the size stays the same.

ONly when creating new spreadsheet and then copying and pasting the 8 rows
into there and then saving this new file results in a file of 15K size.

Any idea how to release memory from a large image object that has been
deleted, but its memory is still lingering in a library of some sort witiin
that xls?

thx
Arjen
"Dave Peterson" wrote:

Maybe resetting the last used cell would help:

Debra Dalgleish shows techniques to reset that last used cell:
http://contextures.com/xlfaqApp.html#Unused



MMcCullough wrote:

When I delete information from a large number of cells (or clear contents) on
a worksheet, the amount of memory allocated and the file size of the
application apparently is not re-allocated to reflect that these cells are no
longer being used. How can I "release" the cells whose contents have been
deleted or cleared? Thanks.
--
Mike McCullough

--

Dave Peterson


--

Dave Peterson

I don't open files.

Maybe you have a used range that's way off from what you think it should be.

Debra Dalgleish shows techniques to reset that last used cell:
http://contextures.com/xlfaqApp.html#Unused

Arjen wrote:

example at http://www.solvesmart.com/xls/

THANKS for the quick reply
I created a button, assigned the macro, and ran it. Nothing showed up. I
also went to EDIT - GOTO SPECIAL and selected OBJECTS, also nothing.

I ahve in the meanwhile inserted a new worksheet SHEET1, and deleted the
oringial sheet all together. the good thing should be, there is no old sheet,
the bad thing might be that i have distroyed the only link to the lingereing
memory

Perhaps then only via VB i can delete it, by releasing all memory of all
objects, if i knew where it was hiding

I have about 100 files that all have a large image that i will have to
remove, and i also have to reduce the filesizes from 2 MB to 15 or so K for
each.

I have posted the test spreadsheet on http://www.solvesmart.com/xls/
as TEST01.xls

Let me know if there is still hope, also considering that hte other 99 files
actually have the picture object still visibily sitting there, and i wil
lhave to find a method to delete those. This testing file is just showing the
after math of deleting the object from the first file

THanks

"Dave Peterson" wrote:

Are you sure you got all the shapes?

Maybe you have a few (or one giant) shape that's not visible. A little macro
may help find those shapes.

Option Explicit
Sub testme()
Dim myShape As Shape
For Each myShape In ActiveSheet.Shapes
myShape.Visible = True
Next myShape
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Arjen wrote:

i have a similar problem

when removing a large picture form the spreadsheet the filesize stays big,
2MB rather than 15K

I have copied over the rows (1-8) that were not part of the image object
(kind of hard to find whre the object lives) into a new worksheet in the same
file, but the size stays the same.

ONly when creating new spreadsheet and then copying and pasting the 8 rows
into there and then saving this new file results in a file of 15K size.

Any idea how to release memory from a large image object that has been
deleted, but its memory is still lingering in a library of some sort witiin
that xls?

thx
Arjen
"Dave Peterson" wrote:

Maybe resetting the last used cell would help:

Debra Dalgleish shows techniques to reset that last used cell:
http://contextures.com/xlfaqApp.html#Unused



MMcCullough wrote:

When I delete information from a large number of cells (or clear contents) on
a worksheet, the amount of memory allocated and the file size of the
application apparently is not re-allocated to reflect that these cells are no
longer being used. How can I "release" the cells whose contents have been
deleted or cleared? Thanks.
--
Mike McCullough

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Thanks
I understand you cannot open files.

basically i have inserted a picture and then removed it. I inserted a blank
SHEET1, then removed the whole existing worksheet. The only thing left is
SHEET1 with nothing on there

THanks

"Dave Peterson" wrote:

I don't open files.

Maybe you have a used range that's way off from what you think it should be.

Debra Dalgleish shows techniques to reset that last used cell:
http://contextures.com/xlfaqApp.html#Unused

Did the techniques at Debra's site make any difference?

Arjen wrote:

Thanks
I understand you cannot open files.

basically i have inserted a picture and then removed it. I inserted a blank
SHEET1, then removed the whole existing worksheet. The only thing left is
SHEET1 with nothing on there

THanks

"Dave Peterson" wrote:

I don't open files.

Maybe you have a used range that's way off from what you think it should be.

Debra Dalgleish shows techniques to reset that last used cell:
http://contextures.com/xlfaqApp.html#Unused

--

Dave Peterson

sorry no it didn't

i actually selected all the cells and hit DELETE.
I went back to the original 2MB file. THre selected EVERYTHING and did the
delete. Then Saved. Closed XLS. Started xls, closed it. Still 2MB

I removed the whole worksheet after inserting a new one. So i only have a
newly entered worksheet with nothing there now, nor anything there before.
The xls is still 2MB. Somewhere outside my worksheet but inside my xls
workbook is still memory allocated.

In MsAcces there is an option to compact the DB, whcih would release it. I
thoguth VB has a command as well that addresses such a thing in XLS

THanks
....Arjen

Did the techniques at Debra's site make any difference?

What you described isn't what Debra's site suggests.

Did you try this on top of the suggestions at Debra's site or instead of?



Arjen wrote:

sorry no it didn't

i actually selected all the cells and hit DELETE.
I went back to the original 2MB file. THre selected EVERYTHING and did the
delete. Then Saved. Closed XLS. Started xls, closed it. Still 2MB

I removed the whole worksheet after inserting a new one. So i only have a
newly entered worksheet with nothing there now, nor anything there before.
The xls is still 2MB. Somewhere outside my worksheet but inside my xls
workbook is still memory allocated.

In MsAcces there is an option to compact the DB, whcih would release it. I
thoguth VB has a command as well that addresses such a thing in XLS

THanks
...Arjen

Did the techniques at Debra's site make any difference?

--

Dave Peterson

i followed her instructions literally, then i did the same in various ways
(deleting the rest of the spreadsheet, or clearing all unused cells

then i did all the other things, including deleting the whole worksheet.

Nothing has decrease the filesize, it looks like there is still an object in
the library, but no link in the visible part of the spreadsheet, or no
visible deletable object

THe only way currently is to copy the used cells and paste them in a new
doucment

Thanks
Arjen

What you described isn't what Debra's site suggests.

Did you try this on top of the suggestions at Debra's site or instead of?

I don't have another guess.

But that last item (copy and paste into a new workbook) usually works.

Arjen wrote:

i followed her instructions literally, then i did the same in various ways
(deleting the rest of the spreadsheet, or clearing all unused cells

then i did all the other things, including deleting the whole worksheet.

Nothing has decrease the filesize, it looks like there is still an object in
the library, but no link in the visible part of the spreadsheet, or no
visible deletable object

THe only way currently is to copy the used cells and paste them in a new
doucment

Thanks
Arjen

What you described isn't what Debra's site suggests.

Did you try this on top of the suggestions at Debra's site or instead of?

--

Dave Peterson