How to reduce an extra large worksheet?

[Machinist60]

My text table was going fine until there was some kind of hiccup resulting in
an abnormal exit. When recovered the file had changed from 18KB to 9496KB
and takes for ever to load. The work sheet instead of being 61 rows by AG
columns is now 61 rows by XFD columns. I cannot even delete one column, it
says "Excel cannot complete this task with the available resources. Choose
less data or close other applications." Task manager says there are about
2.3MB of physical memory available. Is there any way to fix this?

Thanks, John.

 

[JLatham]

A couple of things to try - memory/resources permitting:

Try just copying the used cells in your table to a new worksheet and delete
the old sheet.

Or, try copying the used cels in your table into a new workbook and delete
the huge monstrosity.

or, open a second workbook and set up a linked formula in A1 of a sheet in
it back to A1 in the problem file, then edit the formula to change the $A$1
portion of it to just plain A1 and then fill the formula to the right and
down until all the data from the original table has been echoed. Next, in
the new workbook, select all of the cells with data in them and copy them to
the clipboard (Edit | Copy or Ctrl+[V]) and then, without unselecting them,
use Edit | Paste Special and choose the [Values] option to convert the
formulas to values. Save the new workbook and do what you will with the old
one.

 

[JLatham]

A couple of things to try - memory/resources permitting:

Try just copying the used cells in your table to a new worksheet and delete
the old sheet.

Or, try copying the used cels in your table into a new workbook and delete
the huge monstrosity.

or, open a second workbook and set up a linked formula in A1 of a sheet in
it back to A1 in the problem file, then edit the formula to change the $A$1
portion of it to just plain A1 and then fill the formula to the right and
down until all the data from the original table has been echoed. Next, in
the new workbook, select all of the cells with data in them and copy them to
the clipboard (Edit | Copy or Ctrl+[V]) and then, without unselecting them,
use Edit | Paste Special and choose the [Values] option to convert the
formulas to values. Save the new workbook and do what you will with the old
one.

 

[Machinist60]

Thanks, the simple copy did the trick. I should have tried that! John.

A couple of things to try - memory/resources permitting:

Try just copying the used cells in your table to a new worksheet and delete
the old sheet.

Or, try copying the used cels in your table into a new workbook and delete
the huge monstrosity.

or, open a second workbook and set up a linked formula in A1 of a sheet in
it back to A1 in the problem file, then edit the formula to change the $A$1
portion of it to just plain A1 and then fill the formula to the right and
down until all the data from the original table has been echoed. Next, in
the new workbook, select all of the cells with data in them and copy them to
the clipboard (Edit | Copy or Ctrl+[V]) and then, without unselecting them,
use Edit | Paste Special and choose the [Values] option to convert the
formulas to values. Save the new workbook and do what you will with the old
one.

My text table was going fine until there was some kind of hiccup resulting in
an abnormal exit. When recovered the file had changed from 18KB to 9496KB
and takes for ever to load. The work sheet instead of being 61 rows by AG
columns is now 61 rows by XFD columns. I cannot even delete one column, it
says "Excel cannot complete this task with the available resources. Choose
less data or close other applications." Task manager says there are about
2.3MB of physical memory available. Is there any way to fix this?

Thanks, John.

 

[Machinist60]

Thanks, the simple copy did the trick. I should have tried that! John.

A couple of things to try - memory/resources permitting:

Try just copying the used cells in your table to a new worksheet and delete
the old sheet.

Or, try copying the used cels in your table into a new workbook and delete
the huge monstrosity.

or, open a second workbook and set up a linked formula in A1 of a sheet in
it back to A1 in the problem file, then edit the formula to change the $A$1
portion of it to just plain A1 and then fill the formula to the right and
down until all the data from the original table has been echoed. Next, in
the new workbook, select all of the cells with data in them and copy them to
the clipboard (Edit | Copy or Ctrl+[V]) and then, without unselecting them,
use Edit | Paste Special and choose the [Values] option to convert the
formulas to values. Save the new workbook and do what you will with the old
one.

My text table was going fine until there was some kind of hiccup resulting in
an abnormal exit. When recovered the file had changed from 18KB to 9496KB
and takes for ever to load. The work sheet instead of being 61 rows by AG
columns is now 61 rows by XFD columns. I cannot even delete one column, it
says "Excel cannot complete this task with the available resources. Choose
less data or close other applications." Task manager says there are about
2.3MB of physical memory available. Is there any way to fix this?

Thanks, John.

 

[JLatham]

Sometimes we get lucky. Glad this was one of those times for you.

Thanks, the simple copy did the trick. I should have tried that! John.

A couple of things to try - memory/resources permitting:

Try just copying the used cells in your table to a new worksheet and delete
the old sheet.

Or, try copying the used cels in your table into a new workbook and delete
the huge monstrosity.

or, open a second workbook and set up a linked formula in A1 of a sheet in
it back to A1 in the problem file, then edit the formula to change the $A$1
portion of it to just plain A1 and then fill the formula to the right and
down until all the data from the original table has been echoed. Next, in
the new workbook, select all of the cells with data in them and copy them to
the clipboard (Edit | Copy or Ctrl+[V]) and then, without unselecting them,
use Edit | Paste Special and choose the [Values] option to convert the
formulas to values. Save the new workbook and do what you will with the old
one.

My text table was going fine until there was some kind of hiccup resulting in
an abnormal exit. When recovered the file had changed from 18KB to 9496KB
and takes for ever to load. The work sheet instead of being 61 rows by AG
columns is now 61 rows by XFD columns. I cannot even delete one column, it
says "Excel cannot complete this task with the available resources. Choose
less data or close other applications." Task manager says there are about
2.3MB of physical memory available. Is there any way to fix this?

Thanks, John.

 

[JLatham]

Sometimes we get lucky. Glad this was one of those times for you.

Thanks, the simple copy did the trick. I should have tried that! John.

A couple of things to try - memory/resources permitting:

Try just copying the used cells in your table to a new worksheet and delete
the old sheet.

Or, try copying the used cels in your table into a new workbook and delete
the huge monstrosity.

or, open a second workbook and set up a linked formula in A1 of a sheet in
it back to A1 in the problem file, then edit the formula to change the $A$1
portion of it to just plain A1 and then fill the formula to the right and
down until all the data from the original table has been echoed. Next, in
the new workbook, select all of the cells with data in them and copy them to
the clipboard (Edit | Copy or Ctrl+[V]) and then, without unselecting them,
use Edit | Paste Special and choose the [Values] option to convert the
formulas to values. Save the new workbook and do what you will with the old
one.

My text table was going fine until there was some kind of hiccup resulting in
an abnormal exit. When recovered the file had changed from 18KB to 9496KB
and takes for ever to load. The work sheet instead of being 61 rows by AG
columns is now 61 rows by XFD columns. I cannot even delete one column, it
says "Excel cannot complete this task with the available resources. Choose
less data or close other applications." Task manager says there are about
2.3MB of physical memory available. Is there any way to fix this?

Thanks, John.