Clean/delete all cells outside of a range
 

Cells in range a1:m137 contain data.

But, when I use 'Go To Special' and click 'Last Cell', the cursor goes to a cell outside of range a1:m137.

How do I clear/delete all cells outside of range a1:m137?

Clean/delete all cells outside of a range
 

Cells in range a1:m137 contain data.

But, when I use 'Go To Special' and click 'Last Cell', the cursor goes to a
cell outside of range a1:m137.

How do I clear/delete all cells outside of range a1:m137?

Select entire cols and/or rows, Delete both, then Save.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Clean/delete all cells outside of a range
 

That didn't work so I copied range a1:m137 and pasted it into a new spreadsheet.

Now, in the new spreadsheet, when I use 'Go To Special' and click 'Last Cell', the cursor goes to m137.

Clean/delete all cells outside of a range
 

That didn't work so I copied range a1:m137 and pasted it into a new
spreadsheet.

Now, in the new spreadsheet, when I use 'Go To Special' and click 'Last
Cell', the cursor goes to m137.

You only do cols or rows at a time outside the data area! So if Ctrl+End goes
to p150 then...

Select O:P and Delete
Select 138:150 and Delete
Save the file

This works EVERY TIME unless you do something wrong.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Clean/delete all cells outside of a range
 

I selected all columns to the right of m1 and deleted all of those columns.
I then selected all rows below a137 and deleted all of those rows.

I then used 'Go to Special' and clicked "Last cell" but the cursor still went to cells outside of a1.m137.

Clean/delete all cells outside of a range
 

I selected all columns to the right of m1 and deleted all of those columns.
I then selected all rows below a137 and deleted all of those rows.

I then used 'Go to Special' and clicked "Last cell" but the cursor still went
to cells outside of a1.m137.

You don't say you Saved the file after deleting; -*the most important part of
the process!*

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Clean/delete all cells outside of a range
 

After I deleted the rows and columns in the original spreadsheet, I saved the file.

I then opened that file again.

Then I used the "Go to Special" and clicked "Last Cell" and the cursor went to a cell outside of the range.

Clean/delete all cells outside of a range
 

After I deleted the rows and columns in the original spreadsheet, I saved the file.

I then opened that saved file.

Then I used the "Go to Special" and clicked "Last Cell" and the cursor went to a cell outside of the range.

Clean/delete all cells outside of a range
 

After I deleted the rows and columns in the original spreadsheet, I saved the
file.

I then opened that file again.

Then I used the "Go to Special" and clicked "Last Cell" and the cursor went
to a cell outside of the range.

Ok, this has been the process I've been using since Excel5.0 and it hasn't been
replaced yet with some other process! I use Ctrl+End (keyboard equivalent to
'Go to Special:Last Cell') It works as I'm describing it EVERY TIME! Go ahead
and try it; -duplicate the scenario and see what happens...

Not saying the behavior you got is not possible since Excel (MS Office in
general) has had a tendancy to 'misbehave' more than ever with the newer
releases. Just try the exercise for your own benefit so you can see *how it
should behave* normally.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Clean/delete all cells outside of a range
 

I resolved the problem by copying only the range, pasting it into a new spreadsheet and saving the new file.

I then deleted the original file so I can't "duplicate the scenario" and "try the exercise for your own benefit"

Clean/delete all cells outside of a range
 

I resolved the problem by copying only the range, pasting it into a new
spreadsheet and saving the new file.

I then deleted the original file so I can't "duplicate the scenario" and "try
the exercise for your own benefit"

Huh!
You can enter some value outside your table, ClearContents, then Ctrl+End to
see where the LastCell is located. Just delete the rows/cols necessary to
eliminate the last cell, save, then Ctrl+End to see that the lower right corner
of your table is now the LastCell.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Clean/delete all cells outside of a range
 

I resolved the problem by copying only the range, pasting it into a new
spreadsheet and saving the new file.

You mean you 'got around' the problem this way. Seems an awefully inefficient
approach to me!<g

I then deleted the original file so I can't "duplicate the scenario" and
"try the exercise for your own benefit"

Huh!
You can enter some value outside your table, ClearContents, then Ctrl+End to
see where the LastCell is located. Just delete the rows/cols necessary to
eliminate the last cell, save, then Ctrl+End to see that the lower right
corner of your table is now the LastCell.

If you just use 1 cell then you only have to delete that 1 cell.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Clean/delete all cells outside of a range
 

On 2/5/2018 12:02 PM, GS wrote:
I resolved the problem by copying only the range, pasting it into a
new spreadsheet and saving the new file.

You mean you 'got around' the problem this way. Seems an awefully
inefficient approach to me!<g

I then deleted the original file so I can't "duplicate the scenario"
and "try the exercise for your own benefit"

Huh!
You can enter some value outside your table, ClearContents, then
Ctrl+End to see where the LastCell is located. Just delete the
rows/cols necessary to eliminate the last cell, save, then Ctrl+End to
see that the lower right corner of your table is now the LastCell.

If you just use 1 cell then you only have to delete that 1 cell.

Those exercise work "most of the time" but I've found it's possible to
corrupt Excel where stuff like that is "just simply broke" with some
sheets. This seems more and more prevalent with newer release(s).

Most of the time things otherwise function normally.

We ran into one with a spreadsheet in which as far as could be told by
any way could be done seemed perfectly normal and functioned as should
(other than speed) but this particular version took up some 60MB of disk
storage.

I ran every MS and third party diagnostic on it I could find and none
could find anything to report. Like GARYWC here, the only way I could
find to resolve it was to copy values to a new sheet; pasting cell
content would reproduce the symptom so whatever it was was some huge
memory blob that simply couldn't get rid of any other way I could find.

--

Clean/delete all cells outside of a range
 

On 2/5/2018 12:02 PM, GS wrote:
I resolved the problem by copying only the range, pasting it into a new
spreadsheet and saving the new file.

You mean you 'got around' the problem this way. Seems an awefully
inefficient approach to me!<g

I then deleted the original file so I can't "duplicate the scenario" and
"try the exercise for your own benefit"

Huh!
You can enter some value outside your table, ClearContents, then Ctrl+End
to see where the LastCell is located. Just delete the rows/cols necessary
to eliminate the last cell, save, then Ctrl+End to see that the lower
right corner of your table is now the LastCell.

If you just use 1 cell then you only have to delete that 1 cell.

Those exercise work "most of the time" but I've found it's possible to
corrupt Excel where stuff like that is "just simply broke" with some sheets.
This seems more and more prevalent with newer release(s).

I definitely agree as this has been my experience as well as many others. I see
this more so with Win10 than any other OS, regardless of whether it's the host
OS or running in a VM.

Most of the time things otherwise function normally.

We ran into one with a spreadsheet in which as far as could be told by any
way could be done seemed perfectly normal and functioned as should (other
than speed) but this particular version took up some 60MB of disk storage.

I ran every MS and third party diagnostic on it I could find and none could
find anything to report. Like GARYWC here, the only way I could find to
resolve it was to copy values to a new sheet; pasting cell content would
reproduce the symptom so whatever it was was some huge memory blob that
simply couldn't get rid of any other way I could find.

One thing I found with Excel file size is that formatting entire cols/rows
fills the entire sheet respectively, and results a massive bloat in file size.
Same results when applying some of the cell settings to entire cols/rows.

According to GaryWC, the standard process for removing extraneous cells from
UsedRange didn't work for him. I've never seen that before but doesn't surprise
me with these 'buggy' newer releases and anything running on Win10. I've been
running the same MSO2003/2010 software since XP; -all worked consistently and
reliably until I put them on a Win10 machine. After 9 months of maddening
frustration I replaced the machine with this Win7Pro unit. I'll be replacing it
shortly with another Win10 machine with dedicated graphics memory (needed for
CAD software) but I'll replace that OS 1st off w/Win7Pro before doing anything
else.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Clean/delete all cells outside of a range
 

On 2/5/2018 4:35 PM, GS wrote:
....

One thing I found with Excel file size is that formatting entire
cols/rows fills the entire sheet respectively, and results a massive
bloat in file size. Same results when applying some of the cell settings
to entire cols/rows.

That's one possible thing the bookkeeper could plausibly have done. I
_think_ I recall trying to reset all formats back to 'General' and I'm
certain I had deleted everything outside the UsedRange area but that
didn't solve it once whatever it was had occurred.

According to GaryWC, the standard process for removing extraneous cells
from UsedRange didn't work for him. I've never seen that before but
doesn't surprise me with these 'buggy' newer releases and anything
running on Win10. I've been running the same MSO2003/2010 software since
XP; -all worked consistently and reliably until I put them on a Win10
machine. After 9 months of maddening frustration I replaced the machine
with this Win7Pro unit. I'll be replacing it shortly with another Win10
machine with dedicated graphics memory (needed for CAD software) but
I'll replace that OS 1st off w/Win7Pro before doing anything else.

That's basically where I am; I had stubbornly refused to upgrade an old
XP boxen as I had retired from actively consulting so didn't need
anything more powerful than it when it died in December. I replaced
with a Win7Pro machine but because the College where I'm doing so much
pro bono/volunteer work at the moment and look to continue doing is an
all-MS shop I bought the new Office for compatibility. I have _not_
been pleased...

W7P seems reasonably stable altho I've managed to crash it a couple
times; the most annoying thing with it so far is an apparent penchant to
change window focus on a whim for no apparent reason...

I've not had time to try to see about activation to see about putting
the old Office on this machine.

--

Clean/delete all cells outside of a range
 

On 2/5/2018 4:35 PM, GS wrote:
...

One thing I found with Excel file size is that formatting entire cols/rows
fills the entire sheet respectively, and results a massive bloat in file
size. Same results when applying some of the cell settings to entire
cols/rows.

That's one possible thing the bookkeeper could plausibly have done. I
_think_ I recall trying to reset all formats back to 'General' and I'm
certain I had deleted everything outside the UsedRange area but that didn't
solve it once whatever it was had occurred.

According to GaryWC, the standard process for removing extraneous cells
from UsedRange didn't work for him. I've never seen that before but doesn't
surprise me with these 'buggy' newer releases and anything running on
Win10. I've been running the same MSO2003/2010 software since XP; -all
worked consistently and reliably until I put them on a Win10 machine. After
9 months of maddening frustration I replaced the machine with this Win7Pro
unit. I'll be replacing it shortly with another Win10 machine with
dedicated graphics memory (needed for CAD software) but I'll replace that
OS 1st off w/Win7Pro before doing anything else.

That's basically where I am; I had stubbornly refused to upgrade an old XP
boxen as I had retired from actively consulting so didn't need anything more
powerful than it when it died in December. I replaced with a Win7Pro machine

I prefer Win7Pro as it's proved itself to be very stable for me as a 'power
user'. Some colleagues have been advising to not upgrade MSO from v2010 since
it's been fairly stable since its last update.

but because the College where I'm doing so much pro bono/volunteer work at
the moment and look to continue doing is an all-MS shop I bought the new
Office for compatibility. I have _not_ been pleased...

The only issue I found so far is the new audio codec PowerPoint uses isn';t
backward compatible. The old codec is still there but who's going to know what
version another user is using to switch to it; -MS forcing us to upgrade again
by making the new codec the default!

W7P seems reasonably stable altho I've managed to crash it a couple times;
the most annoying thing with it so far is an apparent penchant to change
window focus on a whim for no apparent reason...

I've not had time to try to see about activation to see about putting the old
Office on this machine.

You should be able to put older MSO versions on with their respective unused
activations. These can also be superceded online or by phone when upgrading to
newer hardware.

My XP machine has versions 8 thru 14 installed and working without issue. Now I
only install 11/14 because I pretty much still develop in MSO2003. Found some
issues, though, with VBA6 using the ShellExecute function running under Win10
that doesn't manifest in MSO2010 because it uses VBA7. Interesting that it only
surfaces in VBA6 apps and not my VB6 apps!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Clean/delete all cells outside of a range
 

We ran into one with a spreadsheet in which as far as could be told by any
way could be done seemed perfectly normal and functioned as should (other
than speed) but this particular version took up some 60MB of disk storage.

Keep in mind that the newer X-doc file format is a compound structure of files
'zipped' together as a single file. When these files become corrupt the culprit
is not necessarily going to always be the xls portion of the file structure!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion