|
Can't delete rows
Anyone have any idea why I wouldn't be able to delete rows in a worksheet?
It doesn't seem to be protected for row deletion. I am selecting the whole row (from the left side of the sheet) & then going to the end of the sheet to grab the bottom. I have over 6,000 rows in this one sheet! No idea how it happened or how I can get rid of them (other than copy/paste what I want to save into a new worksheet). Thanks! JoAnn |
Can't delete rows
Rows of what? Data or just blank rows?
An Excel worksheet has 65536 rows and 256 columns(2007 version has over one million rows and 16,384 columns) None of these can be deleted. Data can be deleted or cleared but the rows and columns remain the same number. Gord Dibben MS Excel MVP On Tue, 26 Feb 2008 12:55:05 -0800, JoAnn wrote: Anyone have any idea why I wouldn't be able to delete rows in a worksheet? It doesn't seem to be protected for row deletion. I am selecting the whole row (from the left side of the sheet) & then going to the end of the sheet to grab the bottom. I have over 6,000 rows in this one sheet! No idea how it happened or how I can get rid of them (other than copy/paste what I want to save into a new worksheet). Thanks! JoAnn |
Can't delete rows
Thanks, that explains it! The rows are blank.
My reason for trying to delete them ... the spreadsheet is not set as "shared" but is being stored in MS Sharepoint & for some reason it takes 1-2 minutes to open from there (opens much faster on my PC). Thought the number of blank rows was the reason. Now that I know it's standard for Excel, I'll check our IT department & the Sharepoint Newsgroup to try to find out why this particular workbook opens so slowly when others don't. If you have any idea why that would happen (from an Excel standpoint), please let me know. I don't have any macros in it (yet) & there are only about 80 rows of data in one sheet & 20 in another. Also ... there will be a 3rd "archive" sheet eventually so the workbook will be growing quite large with time so if there's a limit to overall workbook size, please let me know. Thanks for your help! JoAnn "Gord Dibben" wrote: Rows of what? Data or just blank rows? An Excel worksheet has 65536 rows and 256 columns(2007 version has over one million rows and 16,384 columns) None of these can be deleted. Data can be deleted or cleared but the rows and columns remain the same number. Gord Dibben MS Excel MVP On Tue, 26 Feb 2008 12:55:05 -0800, JoAnn wrote: Anyone have any idea why I wouldn't be able to delete rows in a worksheet? It doesn't seem to be protected for row deletion. I am selecting the whole row (from the left side of the sheet) & then going to the end of the sheet to grab the bottom. I have over 6,000 rows in this one sheet! No idea how it happened or how I can get rid of them (other than copy/paste what I want to save into a new worksheet). Thanks! JoAnn |
Can't delete rows
What is the file size now that you have reset the used range on all sheets?
Should be very small if your data is as you state. I don't work with SharePoint at all so can't speak to that. Overall workbook size is a function of the resources you have available. I have heard of workbooks at the 100Mb area. Gord On Wed, 27 Feb 2008 05:27:02 -0800, JoAnn wrote: Thanks, that explains it! The rows are blank. My reason for trying to delete them ... the spreadsheet is not set as "shared" but is being stored in MS Sharepoint & for some reason it takes 1-2 minutes to open from there (opens much faster on my PC). Thought the number of blank rows was the reason. Now that I know it's standard for Excel, I'll check our IT department & the Sharepoint Newsgroup to try to find out why this particular workbook opens so slowly when others don't. If you have any idea why that would happen (from an Excel standpoint), please let me know. I don't have any macros in it (yet) & there are only about 80 rows of data in one sheet & 20 in another. Also ... there will be a 3rd "archive" sheet eventually so the workbook will be growing quite large with time so if there's a limit to overall workbook size, please let me know. Thanks for your help! JoAnn "Gord Dibben" wrote: Rows of what? Data or just blank rows? An Excel worksheet has 65536 rows and 256 columns(2007 version has over one million rows and 16,384 columns) None of these can be deleted. Data can be deleted or cleared but the rows and columns remain the same number. Gord Dibben MS Excel MVP On Tue, 26 Feb 2008 12:55:05 -0800, JoAnn wrote: Anyone have any idea why I wouldn't be able to delete rows in a worksheet? It doesn't seem to be protected for row deletion. I am selecting the whole row (from the left side of the sheet) & then going to the end of the sheet to grab the bottom. I have over 6,000 rows in this one sheet! No idea how it happened or how I can get rid of them (other than copy/paste what I want to save into a new worksheet). Thanks! JoAnn |
Can't delete rows
The file size is 20,073KB.
What do you mean by "reset the used range on all sheets"? I haven't done that since I didn't know I had to and don't know how to do it. I couldn't find anything in Help on it. Please explain what I have to do. The only ranges I have set are the sections I'm using for calculations. But when I format or add a formula to a cell/column/row, I generally copy it down so it exists in all worksheet rows/columns (I use the keyboard sequence Ctrl-Shift-DownArrow to select them then paste my formula or formatting change). That's when I noticed that I had a lot of rows. Could that be causing a problem? Thanks for all your help with this! JoAnn -- JoAnn "Gord Dibben" wrote: What is the file size now that you have reset the used range on all sheets? Should be very small if your data is as you state. I don't work with SharePoint at all so can't speak to that. Overall workbook size is a function of the resources you have available. I have heard of workbooks at the 100Mb area. Gord On Wed, 27 Feb 2008 05:27:02 -0800, JoAnn wrote: Thanks, that explains it! The rows are blank. My reason for trying to delete them ... the spreadsheet is not set as "shared" but is being stored in MS Sharepoint & for some reason it takes 1-2 minutes to open from there (opens much faster on my PC). Thought the number of blank rows was the reason. Now that I know it's standard for Excel, I'll check our IT department & the Sharepoint Newsgroup to try to find out why this particular workbook opens so slowly when others don't. If you have any idea why that would happen (from an Excel standpoint), please let me know. I don't have any macros in it (yet) & there are only about 80 rows of data in one sheet & 20 in another. Also ... there will be a 3rd "archive" sheet eventually so the workbook will be growing quite large with time so if there's a limit to overall workbook size, please let me know. Thanks for your help! JoAnn "Gord Dibben" wrote: Rows of what? Data or just blank rows? An Excel worksheet has 65536 rows and 256 columns(2007 version has over one million rows and 16,384 columns) None of these can be deleted. Data can be deleted or cleared but the rows and columns remain the same number. Gord Dibben MS Excel MVP On Tue, 26 Feb 2008 12:55:05 -0800, JoAnn wrote: Anyone have any idea why I wouldn't be able to delete rows in a worksheet? It doesn't seem to be protected for row deletion. I am selecting the whole row (from the left side of the sheet) & then going to the end of the sheet to grab the bottom. I have over 6,000 rows in this one sheet! No idea how it happened or how I can get rid of them (other than copy/paste what I want to save into a new worksheet). Thanks! JoAnn |
Can't delete rows
Apologies for the lack of direction about re-setting the used range on a sheet.
The reason you have such a large used range is the copying of formulas down and across far more rows and columns than you may reasonably need. To reset the used range, go to the bottom of your actual data. Select the row below and SHIFT + End + DownArrow EditDeleteEntire Row. Do same for all columns to the right of your data. Do this on all sheets. Now......important part.........Save/Close and reopen. What is size of workbook now? To address the original problem, which is having formulas :just in case" you may want to read up on "Dynamic Ranges" at Debra Dalgleish's site. http://www.contextures.on.ca/xlNames01.html#Dynamic Formulas can be entered that will look at only the used range. Gord On Wed, 27 Feb 2008 11:23:03 -0800, JoAnn wrote: The file size is 20,073KB. What do you mean by "reset the used range on all sheets"? I haven't done that since I didn't know I had to and don't know how to do it. I couldn't find anything in Help on it. Please explain what I have to do. The only ranges I have set are the sections I'm using for calculations. But when I format or add a formula to a cell/column/row, I generally copy it down so it exists in all worksheet rows/columns (I use the keyboard sequence Ctrl-Shift-DownArrow to select them then paste my formula or formatting change). That's when I noticed that I had a lot of rows. Could that be causing a problem? Thanks for all your help with this! JoAnn |
Can't delete rows
That did it!!! It's down to 93KB now.
Thank you so much for all your help! -- JoAnn "Gord Dibben" wrote: Apologies for the lack of direction about re-setting the used range on a sheet. The reason you have such a large used range is the copying of formulas down and across far more rows and columns than you may reasonably need. To reset the used range, go to the bottom of your actual data. Select the row below and SHIFT + End + DownArrow EditDeleteEntire Row. Do same for all columns to the right of your data. Do this on all sheets. Now......important part.........Save/Close and reopen. What is size of workbook now? To address the original problem, which is having formulas :just in case" you may want to read up on "Dynamic Ranges" at Debra Dalgleish's site. http://www.contextures.on.ca/xlNames01.html#Dynamic Formulas can be entered that will look at only the used range. Gord On Wed, 27 Feb 2008 11:23:03 -0800, JoAnn wrote: The file size is 20,073KB. What do you mean by "reset the used range on all sheets"? I haven't done that since I didn't know I had to and don't know how to do it. I couldn't find anything in Help on it. Please explain what I have to do. The only ranges I have set are the sections I'm using for calculations. But when I format or add a formula to a cell/column/row, I generally copy it down so it exists in all worksheet rows/columns (I use the keyboard sequence Ctrl-Shift-DownArrow to select them then paste my formula or formatting change). That's when I noticed that I had a lot of rows. Could that be causing a problem? Thanks for all your help with this! JoAnn |
Can't delete rows
Down from 20,037kb to 93kb
You must have filled a lot of cells with formulas<g Good to hear and thanks for the feedback. Again, apologies for the lack of instruction. I had just finished giving the insrtructions to another poster and got the tow of you mixed up. None too uncommon, I might add. Gord Dibben MS Excel MVP On Thu, 28 Feb 2008 09:21:01 -0800, JoAnn wrote: That did it!!! It's down to 93KB now. Thank you so much for all your help! |
Can't delete rows
Thanks for all your help, Gord ... if I can impose on you a little further ...
I'm having trouble creating a dynamic data range for my worksheet data. I have manually selected the rows & columns I want to define as the starting range (A4 through Z109 - I'm ignoring the 1st 3 rows since I use them as headers). Then I used Insert Name Define to create the dynamic range (called Used_Data_Range) and entered the following formula: =OFFSET(DOCs!$A$1,0,0,COUNTA(DOCs!$A:$A),COUNTA(DO Cs!$1:$1)) When I check the range, I only get up to Column O and down to Row 110. Even though there is still populated columns beyond it (the rows below are empty). What am I doing wrong? Also ... my understanding is that once this range is set & I start to enter data in row 110, etc., it will automatically extend the range, carrying over all formatting, formulas & attributes from within the range providing I have Extend Data Range Formulas & Formats checked (which I do). Is that correct? Or do I have to do anything else? As usual ... thanks for your help! -- JoAnn "Gord Dibben" wrote: Down from 20,037kb to 93kb You must have filled a lot of cells with formulas<g Good to hear and thanks for the feedback. Again, apologies for the lack of instruction. I had just finished giving the insrtructions to another poster and got the tow of you mixed up. None too uncommon, I might add. Gord Dibben MS Excel MVP On Thu, 28 Feb 2008 09:21:01 -0800, JoAnn wrote: That did it!!! It's down to 93KB now. Thank you so much for all your help! |
Can't delete rows
See in-line responses.
On Fri, 29 Feb 2008 10:05:00 -0800, JoAnn wrote: Thanks for all your help, Gord ... if I can impose on you a little further ... I'm having trouble creating a dynamic data range for my worksheet data. I have manually selected the rows & columns I want to define as the starting range (A4 through Z109 - I'm ignoring the 1st 3 rows since I use them as headers). Do not pre-select any range. The purpose of a dynamic range is to let Excel determine the used range based on the "refers to" formula. Then I used Insert Name Define to create the dynamic range (called Used_Data_Range) and entered the following formula: =OFFSET(DOCs!$A$1,0,0,COUNTA(DOCs!$A:$A),COUNTA(D OCs!$1:$1)) Change to =OFFSET(DOCs!$A$1,3,0,COUNTA(DOCs!$A:$A),COUNTA(DO Cs!$1:$1)) The Offset,3,0 means start 3 cells down from A1 and look down from there. When I check the range, I only get up to Column O and down to Row 110. Even though there is still populated columns beyond it (the rows below are empty) If the COUNTA(DOCs!$1:$1)) which means count across row 1 does not go all the way across, perhaps your headers in row 1 only go to O1 Try entering a row which extends to the last used column. Maybe $4:$4 ? Assuming you have data in A1:Z109 F5......enter Used_Data_Range and see what gets selected. What am I doing wrong? Also ... my understanding is that once this range is set & I start to enter data in row 110, etc., it will automatically extend the range, carrying over all formatting, formulas & attributes from within the range providing I have Extend Data Range Formulas & Formats checked (which I do). Is that correct? Or do I have to do anything else? Yes, the formatting should follow from above when you enter data in last unused row. Not sure where your formulas come into play however? Gord As usual ... thanks for your help! |
Can't delete rows
Once again you've solved the problem! It works great now ... thanks for all
your help! -- JoAnn "Gord Dibben" wrote: See in-line responses. On Fri, 29 Feb 2008 10:05:00 -0800, JoAnn wrote: Thanks for all your help, Gord ... if I can impose on you a little further ... I'm having trouble creating a dynamic data range for my worksheet data. I have manually selected the rows & columns I want to define as the starting range (A4 through Z109 - I'm ignoring the 1st 3 rows since I use them as headers). Do not pre-select any range. The purpose of a dynamic range is to let Excel determine the used range based on the "refers to" formula. Then I used Insert Name Define to create the dynamic range (called Used_Data_Range) and entered the following formula: =OFFSET(DOCs!$A$1,0,0,COUNTA(DOCs!$A:$A),COUNTA(D OCs!$1:$1)) Change to =OFFSET(DOCs!$A$1,3,0,COUNTA(DOCs!$A:$A),COUNTA(DO Cs!$1:$1)) The Offset,3,0 means start 3 cells down from A1 and look down from there. When I check the range, I only get up to Column O and down to Row 110. Even though there is still populated columns beyond it (the rows below are empty) If the COUNTA(DOCs!$1:$1)) which means count across row 1 does not go all the way across, perhaps your headers in row 1 only go to O1 Try entering a row which extends to the last used column. Maybe $4:$4 ? Assuming you have data in A1:Z109 F5......enter Used_Data_Range and see what gets selected. What am I doing wrong? Also ... my understanding is that once this range is set & I start to enter data in row 110, etc., it will automatically extend the range, carrying over all formatting, formulas & attributes from within the range providing I have Extend Data Range Formulas & Formats checked (which I do). Is that correct? Or do I have to do anything else? Yes, the formatting should follow from above when you enter data in last unused row. Not sure where your formulas come into play however? Gord As usual ... thanks for your help! |
Can't delete rows
Good to hear.
Thanks for the feedback. On Tue, 4 Mar 2008 07:40:00 -0800, JoAnn wrote: Once again you've solved the problem! It works great now ... thanks for all your help! |
Can't delete rows
Do you happen to know what to do if the below does not work?
I am using Excel 2003. The file is 7MB and needs to be smaller. I followed your directions below. When I right click the short cut menu options is "Delete Row" When I do that it does not ask to 'delete entire row'. I get an hour glass and in the bottom left corner I get "Calculating Cells: X%" When it reaches 100% I still have 65536 rows and then columns that go to IV Please help :-) "Gord Dibben" wrote: Apologies for the lack of direction about re-setting the used range on a sheet. The reason you have such a large used range is the copying of formulas down and across far more rows and columns than you may reasonably need. To reset the used range, go to the bottom of your actual data. Select the row below and SHIFT + End + DownArrow EditDeleteEntire Row. Do same for all columns to the right of your data. Do this on all sheets. Now......important part.........Save/Close and reopen. What is size of workbook now? To address the original problem, which is having formulas :just in case" you may want to read up on "Dynamic Ranges" at Debra Dalgleish's site. http://www.contextures.on.ca/xlNames01.html#Dynamic Formulas can be entered that will look at only the used range. Gord On Wed, 27 Feb 2008 11:23:03 -0800, JoAnn wrote: The file size is 20,073KB. What do you mean by "reset the used range on all sheets"? I haven't done that since I didn't know I had to and don't know how to do it. I couldn't find anything in Help on it. Please explain what I have to do. The only ranges I have set are the sections I'm using for calculations. But when I format or add a formula to a cell/column/row, I generally copy it down so it exists in all worksheet rows/columns (I use the keyboard sequence Ctrl-Shift-DownArrow to select them then paste my formula or formatting change). That's when I noticed that I had a lot of rows. Could that be causing a problem? Thanks for all your help with this! JoAnn |
Can't delete rows
I never mentioned a right-click and delete row but first you may have to
turn off automatic calculation. Then follow Shift + End + Downarrow and EditDeleteEntire Row. Gord On Fri, 4 Sep 2009 11:14:01 -0700, jabe813 wrote: Do you happen to know what to do if the below does not work? I am using Excel 2003. The file is 7MB and needs to be smaller. I followed your directions below. When I right click the short cut menu options is "Delete Row" When I do that it does not ask to 'delete entire row'. I get an hour glass and in the bottom left corner I get "Calculating Cells: X%" When it reaches 100% I still have 65536 rows and then columns that go to IV Please help :-) "Gord Dibben" wrote: Apologies for the lack of direction about re-setting the used range on a sheet. The reason you have such a large used range is the copying of formulas down and across far more rows and columns than you may reasonably need. To reset the used range, go to the bottom of your actual data. Select the row below and SHIFT + End + DownArrow EditDeleteEntire Row. Do same for all columns to the right of your data. Do this on all sheets. Now......important part.........Save/Close and reopen. What is size of workbook now? To address the original problem, which is having formulas :just in case" you may want to read up on "Dynamic Ranges" at Debra Dalgleish's site. http://www.contextures.on.ca/xlNames01.html#Dynamic Formulas can be entered that will look at only the used range. Gord On Wed, 27 Feb 2008 11:23:03 -0800, JoAnn wrote: The file size is 20,073KB. What do you mean by "reset the used range on all sheets"? I haven't done that since I didn't know I had to and don't know how to do it. I couldn't find anything in Help on it. Please explain what I have to do. The only ranges I have set are the sections I'm using for calculations. But when I format or add a formula to a cell/column/row, I generally copy it down so it exists in all worksheet rows/columns (I use the keyboard sequence Ctrl-Shift-DownArrow to select them then paste my formula or formatting change). That's when I noticed that I had a lot of rows. Could that be causing a problem? Thanks for all your help with this! JoAnn |
Can't delete rows
My apologies.
I tried right-click and Edit My option there is EditDelete Row Can you tell me how to turn off automatic calculation? I have already tried clearing formatting and clearing contents in these rows & columns. Thank you! "Gord Dibben" wrote: I never mentioned a right-click and delete row but first you may have to turn off automatic calculation. Then follow Shift + End + Downarrow and EditDeleteEntire Row. Gord On Fri, 4 Sep 2009 11:14:01 -0700, jabe813 wrote: Do you happen to know what to do if the below does not work? I am using Excel 2003. The file is 7MB and needs to be smaller. I followed your directions below. When I right click the short cut menu options is "Delete Row" When I do that it does not ask to 'delete entire row'. I get an hour glass and in the bottom left corner I get "Calculating Cells: X%" When it reaches 100% I still have 65536 rows and then columns that go to IV Please help :-) "Gord Dibben" wrote: Apologies for the lack of direction about re-setting the used range on a sheet. The reason you have such a large used range is the copying of formulas down and across far more rows and columns than you may reasonably need. To reset the used range, go to the bottom of your actual data. Select the row below and SHIFT + End + DownArrow EditDeleteEntire Row. Do same for all columns to the right of your data. Do this on all sheets. Now......important part.........Save/Close and reopen. What is size of workbook now? To address the original problem, which is having formulas :just in case" you may want to read up on "Dynamic Ranges" at Debra Dalgleish's site. http://www.contextures.on.ca/xlNames01.html#Dynamic Formulas can be entered that will look at only the used range. Gord On Wed, 27 Feb 2008 11:23:03 -0800, JoAnn wrote: The file size is 20,073KB. What do you mean by "reset the used range on all sheets"? I haven't done that since I didn't know I had to and don't know how to do it. I couldn't find anything in Help on it. Please explain what I have to do. The only ranges I have set are the sections I'm using for calculations. But when I format or add a formula to a cell/column/row, I generally copy it down so it exists in all worksheet rows/columns (I use the keyboard sequence Ctrl-Shift-DownArrow to select them then paste my formula or formatting change). That's when I noticed that I had a lot of rows. Could that be causing a problem? Thanks for all your help with this! JoAnn |
Can't delete rows
In xl2003 menus:
Tools|Option|Calculation tab Don't forget to turn it back on to automatic when you're done (well, if that's what you want). jabe813 wrote: My apologies. I tried right-click and Edit My option there is EditDelete Row Can you tell me how to turn off automatic calculation? I have already tried clearing formatting and clearing contents in these rows & columns. Thank you! "Gord Dibben" wrote: I never mentioned a right-click and delete row but first you may have to turn off automatic calculation. Then follow Shift + End + Downarrow and EditDeleteEntire Row. Gord On Fri, 4 Sep 2009 11:14:01 -0700, jabe813 wrote: Do you happen to know what to do if the below does not work? I am using Excel 2003. The file is 7MB and needs to be smaller. I followed your directions below. When I right click the short cut menu options is "Delete Row" When I do that it does not ask to 'delete entire row'. I get an hour glass and in the bottom left corner I get "Calculating Cells: X%" When it reaches 100% I still have 65536 rows and then columns that go to IV Please help :-) "Gord Dibben" wrote: Apologies for the lack of direction about re-setting the used range on a sheet. The reason you have such a large used range is the copying of formulas down and across far more rows and columns than you may reasonably need. To reset the used range, go to the bottom of your actual data. Select the row below and SHIFT + End + DownArrow EditDeleteEntire Row. Do same for all columns to the right of your data. Do this on all sheets. Now......important part.........Save/Close and reopen. What is size of workbook now? To address the original problem, which is having formulas :just in case" you may want to read up on "Dynamic Ranges" at Debra Dalgleish's site. http://www.contextures.on.ca/xlNames01.html#Dynamic Formulas can be entered that will look at only the used range. Gord On Wed, 27 Feb 2008 11:23:03 -0800, JoAnn wrote: The file size is 20,073KB. What do you mean by "reset the used range on all sheets"? I haven't done that since I didn't know I had to and don't know how to do it. I couldn't find anything in Help on it. Please explain what I have to do. The only ranges I have set are the sections I'm using for calculations. But when I format or add a formula to a cell/column/row, I generally copy it down so it exists in all worksheet rows/columns (I use the keyboard sequence Ctrl-Shift-DownArrow to select them then paste my formula or formatting change). That's when I noticed that I had a lot of rows. Could that be causing a problem? Thanks for all your help with this! JoAnn -- Dave Peterson |
Can't delete rows
I have tried everything to delete these rows in order to reduce the file size.
Tried a variation of similar direction: http://www.contextures.com/xlfaqApp.html#Unused Any suggestions?? Anyone? "Gord Dibben" wrote: I never mentioned a right-click and delete row but first you may have to turn off automatic calculation. Then follow Shift + End + Downarrow and EditDeleteEntire Row. Gord On Fri, 4 Sep 2009 11:14:01 -0700, jabe813 wrote: Do you happen to know what to do if the below does not work? I am using Excel 2003. The file is 7MB and needs to be smaller. I followed your directions below. When I right click the short cut menu options is "Delete Row" When I do that it does not ask to 'delete entire row'. I get an hour glass and in the bottom left corner I get "Calculating Cells: X%" When it reaches 100% I still have 65536 rows and then columns that go to IV Please help :-) "Gord Dibben" wrote: Apologies for the lack of direction about re-setting the used range on a sheet. The reason you have such a large used range is the copying of formulas down and across far more rows and columns than you may reasonably need. To reset the used range, go to the bottom of your actual data. Select the row below and SHIFT + End + DownArrow EditDeleteEntire Row. Do same for all columns to the right of your data. Do this on all sheets. Now......important part.........Save/Close and reopen. What is size of workbook now? To address the original problem, which is having formulas :just in case" you may want to read up on "Dynamic Ranges" at Debra Dalgleish's site. http://www.contextures.on.ca/xlNames01.html#Dynamic Formulas can be entered that will look at only the used range. Gord On Wed, 27 Feb 2008 11:23:03 -0800, JoAnn wrote: The file size is 20,073KB. What do you mean by "reset the used range on all sheets"? I haven't done that since I didn't know I had to and don't know how to do it. I couldn't find anything in Help on it. Please explain what I have to do. The only ranges I have set are the sections I'm using for calculations. But when I format or add a formula to a cell/column/row, I generally copy it down so it exists in all worksheet rows/columns (I use the keyboard sequence Ctrl-Shift-DownArrow to select them then paste my formula or formatting change). That's when I noticed that I had a lot of rows. Could that be causing a problem? Thanks for all your help with this! JoAnn |
Can't delete rows
I repeat my offer
-- Don Guillett Microsoft MVP Excel SalesAid Software "jabe813" wrote in message ... I have tried everything to delete these rows in order to reduce the file size. Tried a variation of similar direction: http://www.contextures.com/xlfaqApp.html#Unused Any suggestions?? Anyone? "Gord Dibben" wrote: I never mentioned a right-click and delete row but first you may have to turn off automatic calculation. Then follow Shift + End + Downarrow and EditDeleteEntire Row. Gord On Fri, 4 Sep 2009 11:14:01 -0700, jabe813 wrote: Do you happen to know what to do if the below does not work? I am using Excel 2003. The file is 7MB and needs to be smaller. I followed your directions below. When I right click the short cut menu options is "Delete Row" When I do that it does not ask to 'delete entire row'. I get an hour glass and in the bottom left corner I get "Calculating Cells: X%" When it reaches 100% I still have 65536 rows and then columns that go to IV Please help :-) "Gord Dibben" wrote: Apologies for the lack of direction about re-setting the used range on a sheet. The reason you have such a large used range is the copying of formulas down and across far more rows and columns than you may reasonably need. To reset the used range, go to the bottom of your actual data. Select the row below and SHIFT + End + DownArrow EditDeleteEntire Row. Do same for all columns to the right of your data. Do this on all sheets. Now......important part.........Save/Close and reopen. What is size of workbook now? To address the original problem, which is having formulas :just in case" you may want to read up on "Dynamic Ranges" at Debra Dalgleish's site. http://www.contextures.on.ca/xlNames01.html#Dynamic Formulas can be entered that will look at only the used range. Gord On Wed, 27 Feb 2008 11:23:03 -0800, JoAnn wrote: The file size is 20,073KB. What do you mean by "reset the used range on all sheets"? I haven't done that since I didn't know I had to and don't know how to do it. I couldn't find anything in Help on it. Please explain what I have to do. The only ranges I have set are the sections I'm using for calculations. But when I format or add a formula to a cell/column/row, I generally copy it down so it exists in all worksheet rows/columns (I use the keyboard sequence Ctrl-Shift-DownArrow to select them then paste my formula or formatting change). That's when I noticed that I had a lot of rows. Could that be causing a problem? Thanks for all your help with this! JoAnn |
Can't delete rows
After deleting unused rows and columns and saving, closing and reopening
your file size is still very large? What is current file size under FilePropertiesGeneral? You will always have 256 columns and 65536 rows so don't think that deleting will remove those cells. Gord On Wed, 9 Sep 2009 08:28:05 -0700, jabe813 wrote: I have tried everything to delete these rows in order to reduce the file size. Tried a variation of similar direction: http://www.contextures.com/xlfaqApp.html#Unused Any suggestions?? Anyone? "Gord Dibben" wrote: I never mentioned a right-click and delete row but first you may have to turn off automatic calculation. Then follow Shift + End + Downarrow and EditDeleteEntire Row. Gord On Fri, 4 Sep 2009 11:14:01 -0700, jabe813 wrote: Do you happen to know what to do if the below does not work? I am using Excel 2003. The file is 7MB and needs to be smaller. I followed your directions below. When I right click the short cut menu options is "Delete Row" When I do that it does not ask to 'delete entire row'. I get an hour glass and in the bottom left corner I get "Calculating Cells: X%" When it reaches 100% I still have 65536 rows and then columns that go to IV Please help :-) "Gord Dibben" wrote: Apologies for the lack of direction about re-setting the used range on a sheet. The reason you have such a large used range is the copying of formulas down and across far more rows and columns than you may reasonably need. To reset the used range, go to the bottom of your actual data. Select the row below and SHIFT + End + DownArrow EditDeleteEntire Row. Do same for all columns to the right of your data. Do this on all sheets. Now......important part.........Save/Close and reopen. What is size of workbook now? To address the original problem, which is having formulas :just in case" you may want to read up on "Dynamic Ranges" at Debra Dalgleish's site. http://www.contextures.on.ca/xlNames01.html#Dynamic Formulas can be entered that will look at only the used range. Gord On Wed, 27 Feb 2008 11:23:03 -0800, JoAnn wrote: The file size is 20,073KB. What do you mean by "reset the used range on all sheets"? I haven't done that since I didn't know I had to and don't know how to do it. I couldn't find anything in Help on it. Please explain what I have to do. The only ranges I have set are the sections I'm using for calculations. But when I format or add a formula to a cell/column/row, I generally copy it down so it exists in all worksheet rows/columns (I use the keyboard sequence Ctrl-Shift-DownArrow to select them then paste my formula or formatting change). That's when I noticed that I had a lot of rows. Could that be causing a problem? Thanks for all your help with this! JoAnn |
| All times are GMT +1. The time now is 07:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com