[vks64]

Sir,

I want to delete all blank rows and columns in my excel worksheet and restrict my workspace to the data filled up area. i.e. only the used area must figure out in the excel sheet.

Can anyone help me please,

Thanking you

vks64

[JBeaucaire]

You have an example workbook with BEFORE/AFTER? Questions like this frequently means something other than what is presented, so best if you show us examples of your data, before and after the deletions.

[vks64]

You have an example workbook with BEFORE/AFTER? Questions like this frequently means something other than what is presented, so best if you show us examples of your data, before and after the deletions.

Sir ,
I could not attach a worksheet showing the result, because I could not prepare the results in excel sheet. Hence, I had attached one worksheet with input data and was narrating my requirement as under:-

The sample excel sheet has some data filled up in a field between columns
A1 to A10 and E1 to E10 (an area covering 50cells in the rectangle- 10x5 cells). The requirement is to exhibit/show only those 50 cells in the resultant excel sheet (with filled up data) and the remaining cells of the worksheet getting hidden or deleted whichever way is possible. In the end, the resultant worksheet will be a small worksheet of dimension 10x5 cells. i.e.. all the cells (columns and rows) except those 50 cells gets vanished.

Reason: I use a full worksheet with large amount of data. When I copy and paste complicated formulas into the excel sheet, the excel sheet gets slower and takes considerable time to process the commands. Once it is closed and reopened it takes a longer time to re-open. In some cases, while I work on the worksheet, It expresses its inability to work further stating that ‘excel sheet could not complete the process because of huge volume of data’ etc. and insists that some of the data/sheets have to closed for further processing. In the end, the sheet does not save the data processed up to that point of time. All the calculations done up to that point of time gets lost, since the same could not be saved. During such times, the excel sheet will NOT allow me to copy or paste or delete any further data. During such times, I have to drop all my process at that point of time and close the excel sheet and I have to begin the work once again and redo all the computation processes one by one and save the data at regular but smaller intervals of time. I believe the problem to be that the system works out the formula for the entire sheet (including blank and unfilled area) and hence the computation of data for the whole area makes excel sheet unable to process the commands (formulas), save the document etc. and hence its gets slower. I use MS Office 2010 trial version.

What is the problem for all these. Can anyone help on these?

I also have the following presumptions:-

1) The daily excel sheet I work every day, say "sheet A" is either infected or corrupted. But here also, as I close and reopen my excel worksheet and re-do the computations work piece meal it works.

2) The excel sheet "Sheet A", I work every day was created afresh (everyday) by copying huge volumes of data with formulas from a number of other worksheets using copy and paste special function. Here I believe that the daily work sheet "Sheet A" with which I work would have created/ established links (without permission) with other excel sheets, as when the data with formulas were copied and pasted using special function. As a result, the working of the excel sheet would have got delayed because the daily work sheet "sheet A" goes to the linked worksheet every time for verification causing workload and delay.
If the presumption is logical and correct, how this problem could be solved and the links removed? Also how to prevent the establishing of links?
How to confirm the presence of links between the daily work sheet "sheet A" and the source excel sheets from where the data with formulas was copied and pasted ?

Hope I could explain my problem,

Please help me in getting a solution!

Thank you
vks64

[JBeaucaire]

Use the EDIT LINKS option to change/delete links to other workbooks.

2012-12-06_0734 - JerryBeaucaire's library

[vks64]

Sir,

1)The first part of the question is cut and pasted as quote;

Please help with your reply,

2)Also please help me locate the solution, posted in your reply,

"EDIT LINKS option to change/delete links to other workbooks."


Thank you
vks64


Sir ,

I could not attach a worksheet showing the result, because I could not prepare the results in excel sheet. Hence, I had attached one worksheet with input data and was narrating my requirement as under:-

The sample excel sheet has some data filled up in a field between columns
A1 to A10 and E1 to E10 (an area covering 50cells in the rectangle- 10x5 cells). The requirement is to exhibit/show only those 50 cells in the resultant excel sheet (with filled up data) and the remaining cells of the worksheet getting hidden or deleted whichever way is possible. In the end, the resultant worksheet will be a small worksheet of dimension 10x5 cells. ie., all the cells (columns and rows) except those 50 cells gets vanished.

[JBeaucaire]

There is a link in post #4 you should click to assist finding EDIT LINKS.

You can also press F1 in Excel and type in EDIT LINKS to get Excel's help on finding it.

[vks64]

Helpful sir,
I checked my excel sheet and found that there was no links.
I have the following two requirements:-
I want to work further towards the same and to identify the problem in my excel sheet discussed earlier and re-produced below for immediate reference as quotes 1 and 2 :-
Quote 1
Sir,
I could not attach a worksheet showing the result, because I could not prepare the results in excel sheet. Hence, I had attached one worksheet with input data and was narrating my requirement as under:-
The sample excel sheet has some data filled up in a field between columns A1 to A10 and E1 to E10 (an area covering 50cells in the rectangle of 10x5 cells). The requirement is to exhibit/show only those 50 cells in the resultant excel sheet (with filled up data) and the remaining cells of the worksheet getting hidden or deleted whichever way is possible. In the end, the resultant worksheet will be a small worksheet of dimension 10x5 cells. ie., all the cells (columns and rows) except those 50 cells gets vanished.
Sir,
I need a solution because I want to reduce the excel sheet (its size) to the workspace in which data is filled up for faster manoeuvre.
Quote 2
I use a full worksheet (nearly 1250 rowsx40 columns) with large amount of data. When I copy and paste complicated formulas into the excel sheet, the excel sheet gets slower and takes considerable time to process the commands. Once it is closed and re-opened it takes a longer time to re-open. In some cases, while I work on the worksheet, It expresses its inability to work further stating that “excel sheet could not complete the process because of huge volume of data” etc., and insists that some of the data/sheets have to closed for further processing. In the end, the sheet does not save the data processed up to that point of time. All the calculations done up to that point of time gets lost, since the same could not be saved. During such times, the excel sheet will NOT allow me to copy or paste or delete any further data and has to be closed forcibly. During such times, I have to drop all my process at that point of time and close the excel sheet and I have to begin the work once again and redo all the computation processes one by one and save the data at regular but smaller intervals of time. I believe the problem to be that the system works out the formula for the entire sheet (including blank and unfilled area) and hence the computation of data for the whole area makes excel sheet unable to process the commands (formulas), save the document etc., and hence its gets slower. I use MS Office 2010 trial version. My laptop was Acer make 5740 model with Pentium I3 processor.

What is the problem for all these? Can you help on these?

In between I was enclosing another sample sheet which shows one of the problem reflected in the excel sheet.

Sorry for the disturbance and lengthy discussion

Many Thanks for the involvement you have shown over this problem!!
Thank you once again,
vks64

[vks64]

Sir,

I have attached pdf sheet showing the screen print image of problems faced in excel sheet.
Please help me with a solution
vks64

[JBeaucaire]

A pentium computer is completely inadequate apparently for the version of MSOffice you are running and the size/complexity of the data you are crunching.

You should be working on a minimum of a Quad-core processor (standard today in all computers) and the latest OS. Also, the computer should have the maximum memory installed for that computer.

There is no substitute for having the proper tools for the jobs at hand. I replace my work computers every 3 years for this very reason. My current laptopn is very powerful and bought online for less than $400, well worth the expense.

In the meantime, your only hope is to change your formulas to the calculate the minimal number of rows, not entire columns.

My video in post #4 showing you where to find the Edit Links is the best I can do. If you still can't find it after that, then I won't be able to help any better than that.

[vks64]

Thank you sir for your continuos replies,

One small mistake regarding the processor, It is intel core i3 processor with windows 7 OS and Not Pentium I3 as mistakenly posted. I believe it is reasonably latest.

I accept your sugestion and I will replace my computer, as and when I find it does not give me the best

Thank You once again for bearing the troubles I have posted, sir

vks64

[vks64]

Use the EDIT LINKS option to change/delete links to other workbooks.

2012-12-06_0734 - JerryBeaucaire's library

Sir,

As suggested, I have checked the workbook but the EDIT LINKS option was found to be “Not enabled”. I believe that there were no links.

However, I found that there are numerous work book connections which I could delete one by one only. It is really enormous and time consuming and could not be deleted manually. i.e.. all the workbook connections could not be selected in one single stroke and deleted.

Can you help me sir, to delete these work book connections which are numerous in numbers? I believe that the work book connections would have got imported while was I importing text data. And deletion of the same may remove the slowness of the excel sheet I experience?

Thank You,
vks64

[dontknownuthin]

To delete a column or row in Excel I believe you just highlight that column or row and either right click for your options and choose delete, or just hit the delete button. To select the area you wish to print, you highlight that area, go into page formatting and select it as your print area.

Try these things and see if they work for you.

[vks64]

To delete a column or row in Excel I believe you just highlight that column or row and either right click for your options and choose delete, or just hit the delete button. To select the area you wish to print, you highlight that area, go into page formatting and select it as your print area.

Try these things and see if they work for you.

Sir,

The problem is regarding deleting numerous rows under Data>connections>workbook connections of MS Excel 2010. i.e.. removeal of existing data connections. The MS Excel 2010 does not allow for the selection/highlighting of all the rows under the above. Only one row could be selected and deleted at a time. Because of the presence of numerous rows, deletions of all the rows, manually becomes difficult if not possible. As a result the excel sheet gets slower; Every time the excel sheet is opened fresh the message "'Security warning Data Connections have been disabled [ Enable content]'. " appears.

In the end, the excel sheet gets slower to open, save and close etc.

vks64